Excel - changing cell reference via another cell

Asked By chicolini on 06-May-10 11:53 AM
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 do not 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 replied to chicolini on 06-May-10 12:43 PM
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.
--
HTH...

Jim Thomlinson
Luke M replied to chicolini on 06-May-10 01:05 PM
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

--
Best Regards,

Luke M
Roger Govier replied to chicolini on 07-May-10 05:21 AM
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 D1:D65536 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.
--
Regards
Roger Govier