Drag down the cell links of multiple sheets

[Narnimar]

I am looking for method of linking the cells of various cells from various
sheets I formatted identically.
A1 B1 C1 D1
E1 F1
='sheet2'!$A$1 ='sheet2'!$B$1 ='sheet2'!$C$1 =sheet2'!$D$1
='sheet2'!$E$1 ='sheet2'!$F$1
='sheet3'!$A$1 ='sheet3'!$B$1 ='sheet3'!$C$1 ='sheet3'!$E$1
='sheet3'!$E$1 ='sheet3'!$F$1
and so on... if I drag the cell A1 which is ='sheet2'!$A$1 to A2 then the
cell A2 should give the result ='sheet3'!$A$1

I tried by dragging down the linked cells of A1 but it does not link to
sheet2 and so on . How to get that done without macro?

 

[Gord Dibben]

INDIRECT function is your friend.

=INDIRECT("Sheet" & (ROW()) & "!A1") entered in A1 of target sheet.


Gord Dibben MS Excel MVP

 

[Narnimar]

Dear Gord Dibben,
Thanks a lot.

INDIRECT function is your friend.

=INDIRECT("Sheet" & (ROW()) & "!A1") entered in A1 of target sheet.


Gord Dibben MS Excel MVP

 

[GoBow777]

Hello Narnimar:

I think the INDIRECT function is what you might be looking for. Past
the formula in the desired location and type the sheet numbers i
column K, then drag the formula down as far as needed.


Code

 

[Narnimar]

Hi Gord Dibben,
I get #REF! returns where I delete (by doing Ctrl-) the row in any of data
sheets. If I delete that row #REF! which is in the result sheet the next row
will be filled with #REF!. Please help again.
Thanks.

 

[Gord Dibben]

Cannot replicate.

I delete row 1 in Sheet2 and the formula on target sheet still refers to A1

Where are the INDIRECT formulas located? What is the name of the target sheet.

Which rows are you deleting on which sheet(s)?


Gord