Hi,
I have a sheet with the name of: June 2006
In an other sheet called (Report) in the same workbook in cell C25
I have the following formula: ='June 2006'!E$160*'June 2006'!$D$1+'June
2006'!F$160
I need to copy that formula to the next 73 cells (aproximatly) leaving lines
for subtotal between.
to clarify:
C26 will have: ='June 2006'!G$160*'June 2006'!$D$1+'June 2006'!H$160
C27 will have: ='June 2006'!I$160*'June 2006'!$D$1+'June 2006'!J$160
C28 will have: ='June 2006'!K$160*'June 2006'!$D$1+'June 2006'!L$160
.................. ...........
............................
The last cell will have the formula of:
='June 2006'!EG$160*'June 2006'!$D$1+'June
2006'!EH$160

Is there an easy way to do it? I am going to do this for 12 sheets (12
months).

Have a look at the INDIRECT and OFFSET functions in Excel Help.

Hope this helps.

Pete

On May 21, 10:45*am, "Khalil handal" <khhan...@yahoo.com> wrote:
> Hi,
> I have a sheet with the name of: *June 2006
> In an other sheet called (Report) in the same workbook in cell C25
> I have the following formula: * ='June 2006'!E$160*'June 2006'!$D$1+'June
> 2006'!F$160
> I need to copy that formula to the next 73 cells (aproximatly) leaving lines
> for subtotal between.
> to clarify:
> C26 will have: * ='June 2006'!G$160*'June 2006'!$D$1+'June 2006'!H$160
> C27 will have: *='June 2006'!I$160*'June 2006'!$D$1+'June 2006'!J$160
> C28 will have: *='June 2006'!K$160*'June 2006'!$D$1+'June 2006'!L$160
> ................. *...........
> ...........................
> The last cell will have the formula of:
> * * * * * * * * * * * * ='June 2006'!EG$160*'June 2006'!$D$1+'June
> 2006'!EH$160
>
> Is there an easy way to do it? *I am going to do this for 12 sheets (12
> months).

I don't think you're going to need the INDIRECT function -- just
OFFSET. You will probably also need the ROWS and COLUMNS function.
Write me if you have trouble and I'll help you write the formula.
Thanks,

It is the custom in these news groups to post solutions here where we all may
benefit.

If you have an idea of what your formulas will be, show us an example please.


Gord Dibben MS Excel MVP

On Wed, 21 May 2008 10:28:34 -0700 (PDT), "Excel@shoenfeltconsulting.com"
<excel@shoenfeltconsulting.com> wrote:

>I don't think you're going to need the INDIRECT function -- just
>OFFSET. You will probably also need the ROWS and COLUMNS function.
>Write me if you have trouble and I'll help you write the formula.
>Thanks,

Hi,
I have ssen the Offset function at the help and realized that the: rows,
cols, height and width are to define the range so that it is clear that the
begining and ending cell in the range are known. I think that in my sheet:
C25=='June 2006'!E$160*'June 2006'!$D$1+'June 2006'!F$160
will look like: =OFFSET('June 2006'!D159,1,1,1,2)
This didn't work:
1- the #VALUE error appear.
2- the first cell needs to be multiplied with $D$1 value.

I am confused!


"Excel@shoenfeltconsulting.com" <excel@shoenfeltconsulting.com> wrote in
message
news:6c36c629-4426-4af8-8ead-edac1e570d39@y38g2000hsy.googlegroups.com...
>I don't think you're going to need the INDIRECT function -- just
> OFFSET. You will probably also need the ROWS and COLUMNS function.
> Write me if you have trouble and I'll help you write the formula.
> Thanks,

Hi,
=SUM(OFFSET('June 2006'!D159,1,1,1,2))
sums the values in the cells E160 and F160.
What changes needs to be done to have the vale of cell E160 be multiplied
with the value in cell $D$1?
This might make it more clear to the previous posting


"Excel@shoenfeltconsulting.com" <excel@shoenfeltconsulting.com> wrote in
message
news:6c36c629-4426-4af8-8ead-edac1e570d39@y38g2000hsy.googlegroups.com...
>I don't think you're going to need the INDIRECT function -- just
> OFFSET. You will probably also need the ROWS and COLUMNS function.
> Write me if you have trouble and I'll help you write the formula.
> Thanks,