Copy

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Khalil handal - 21 May 2008 10:45 GMT

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).

Reply to this Message

Pete_UK - 21 May 2008 12:56 GMT

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

Hope this helps.

Pete

> Hi,
> I have a sheet with the name of: June 2006
[quoted text clipped - 15 lines]
> Is there an easy way to do it? I am going to do this for 12 sheets (12
> months).

Reply to this Message

Excel@shoenfeltconsulting.com - 21 May 2008 18:28 GMT

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,

Reply to this Message

Gord Dibben - 21 May 2008 20:48 GMT

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

>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,

Reply to this Message

Khalil Handal - 21 May 2008 21:11 GMT

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!

>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,

Reply to this Message

Khalil Handal - 21 May 2008 21:14 GMT

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

>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,

Reply to this Message