Cell Linking

[Big Ben]

Hi Guys,

I have multiple tabs in a workbook with 3 totals at the bottom of eac
tab which appear as follows:

A B
1 Total 1 Value 1
2 Total 2 Value 2
3 Total 3 Value 3

I also have a summary sheet that I need to link these totals to wit
the following formulas.

A B C
D
1 Tab Name Total 1 Total 2 Total 3
2 Tab 1 =’tab1’!B1 =’tab1’!B2 =’tab1’!B3
3 Tab 2 =’tab2’!B1 =’tab2’!B2 =’tab2’!B3
4 Tab 3 =’tab3’!B1 =’tab3’!B2 =’tab3’!B3

The problem arises because I want to copy these formulas down 195 row
in the summary sheet, however, when I copy this info down, it does no
change the tab #, but rather the row reference. Is there any way I ca
copy this formula down whereby the tab value will change, leaving th
row and column references constant? Or is there any other way to d
this? I have tried absolute referencing, however, this still does no
change the tab value. Any help would be greatly appreciated!

Thanks,

Be

 

[RagDyeR]

If your first formula starts on row2, try this:

=INDIRECT("tab"&ROW()-1&"!$B$1")
--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================


Hi Guys,

I have multiple tabs in a workbook with 3 totals at the bottom of each
tab which appear as follows:

A B
1 Total 1 Value 1
2 Total 2 Value 2
3 Total 3 Value 3

I also have a summary sheet that I need to link these totals to with
the following formulas.

A B C
D
1 Tab Name Total 1 Total 2 Total 3
2 Tab 1 ='tab1'!B1 ='tab1'!B2 ='tab1'!B3
3 Tab 2 ='tab2'!B1 ='tab2'!B2 ='tab2'!B3
4 Tab 3 ='tab3'!B1 ='tab3'!B2 ='tab3'!B3

The problem arises because I want to copy these formulas down 195 rows
in the summary sheet, however, when I copy this info down, it does not
change the tab #, but rather the row reference. Is there any way I can
copy this formula down whereby the tab value will change, leaving the
row and column references constant? Or is there any other way to do
this? I have tried absolute referencing, however, this still does not
change the tab value. Any help would be greatly appreciated!

Thanks,

Ben

 

[Frank Kabel]

Hi
try the following formulas
B2:
=INDIRECT("'" & $A2 & "'!B1")

C2:
=INDIRECT("'" & $A2 & "'!B2")

D2:
=INDIRECT("'" & $A2 & "'!B3")

and copy these formulas down

 

[Big Ben]

Hi guys,

Thanks a lot for your response RagDyeR and Frank. I decided to us
Frank’s formula and it seemed to work quite well. However, I do no
understand how the INDIRECT formula works, in fact, I have never hear
of the indirect function before. Would it be possible for you t
explain this to me please? I really do not understand the formula
especially in respect to how it determines which sheet to look to fo
values. I am very surprised Microsoft does not a simple method to d
relative copying with respect to sheets in a workbook!!!!??

Thanks so much for your help.

Be

 

[Frank Kabel]

Hi
INDIRECT just converts a string value into a real reference. In your
case within INDIRECT a string is created (looking exactly as you would
enter qa cell reference manually). INDIRECT then converts this string
to a real reference

 

[Big Ben]

That makes sense now. Thanks a lot Frank for taking the time to help m
out.

Regards,

Be