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

InsertNameDefine
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

chicolini wrote:
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.