changing cell reference via another cell

[chicolini]

I have a spreadsheet with a tab (called calc) containing columns of
calculations. The next tab (called D) contains a diagram which pulls numbers
off of column D of the calc tab. I copy tab D to a new tab (by right
clicking on the tab and selecting copy) and I rename the new tab as E. I
want the cells in tab E to reference column E in the calc tab. Currently, I
do a Find/Replace, changing 'calc'!D to 'calc'!E. Is there a way I can
automate this so I don't have to do the Find/Replace. I was hoping I could
just type E in a cell on tab E and it would be set up to automatically change
'calc'!D to 'calc'!E in all relevant locations on tab E. Or better yet it
would look at the name of the tab and automatically change to that when I
change the tab name to E. So can I set up the cell references to do this?
Thanks.

 

[Jim Thomlinson]

You can use the indirect function. You can look it up in the functions help.
Issues with doing that are taht your formulas may not be draggable requiring
you to write every formula by hand. Also Indirect is vaoltile meaning that it
has high calculation overhead and could slow your spreadsheet down.

 

[Luke M]

To get the current sheet name into a cell:
=MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,999)

Assuming this is in cell A1, your other formulas would then be something
like:
=INDIRECT("Calc!"&A1&":"&A1)
This would reference Calc!E:E

 

[Roger Govier]

Hi

Since your tab names are only a single character, then you could set up
a named range called myRange, just utilising the rightmost character of
the filename

Insert>Name>Define>
Name myRange
Refers to
=EVALUATE(RIGHT(CELL("filename",!$A$1))&"1:"&RIGHT(CELL("filename",!$A$1))&"65536")

This would produce a range from D165536 on Sheet D and E1:E65536 on
Sheet E.
Make the ranges shorter if required.

Then
=INDEX(myRange,10)
would give the value from row 10 of the relevant column for the sheet.