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?

INDIRECT function is your friend.

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


Gord Dibben MS Excel MVP


On Fri, 4 Jan 2008 01:13:01 -0800, Narnimar
wrote:

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?

Hello Narnimar:

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

Matt

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" wrote:

INDIRECT function is your friend.

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


Gord Dibben MS Excel MVP


On Fri, 4 Jan 2008 01:13:01 -0800, Narnimar
wrote:

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?

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

On Tue, 8 Jan 2008 07:49:03 -0800, Narnimar
wrote:

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" wrote:

INDIRECT function is your friend.

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


Gord Dibben MS Excel MVP


On Fri, 4 Jan 2008 01:13:01 -0800, Narnimar
wrote:

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?