Draging Formula

D

Daniel Morgan

hi

Please Please help me!!!!!
I have a formula that relates to different sheets and cells in a sumary sheet. what i want to do is drag the cells and instead of the cell reference increasing i want the sheet reference to increase

i.e. =Sheet2!A2 draged to; =Sheet3!A2 =Sheet4!A2 et

thank you

Daniel
 
G

Gord Dibben

Daniel

Entered in row 2 of a column.

=INDIRECT("Sheet" & (ROW()) & "!A2")

Gord Dibben Excel MVP
 
M

Max

In a new sheet named: Summary

put in say, A2: =INDIRECT("Sheet"&ROW()&"!A2")

A2 returns the equivalent of : =Sheet2!A2

Copy A2 down col A

The Sheet # will be incremented accordingly as you copy down.

Adjust the "ROW()" part to return the correct Sheet#
to suit, depending on the row in which your start cell is

The example above uses cell A2, which is in row2.

[we just use "ROW()" here,
i.e. no need to further add any number to "ROW()"
since we want "Sheet2"]

------------------------------

If the copying is across (i.e. copy right),

Put in A2: =INDIRECT("Sheet"&COLUMN()+1&"!A2")

A2 again returns the equivalent of : =Sheet2!A2

Copy A2 across row2

The Sheet # will be incremented accordingly.

Adjust the "COLUMN()+1" part to return the correct Sheet#
to suit, depending on the column in which your start cell is

The example above uses cell A2, which is in col #1.

[we have to add "1" to "COLUMN()" as we want "Sheet2"]

--
Rgds
Max
xl 97
----------------------------------
Use xdemechanik <at>yahoo<dot>com for email
-----------------------------------------
Daniel Morgan said:
hi

Please Please help me!!!!!!
I have a formula that relates to different sheets and cells in a sumary
Click to expand...
sheet. what i want to do is drag the cells and instead of the cell reference
increasing i want the sheet reference to increase.
 
Top