If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
changing cell reference via another cell
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. |
#2
|
|||
|
|||
changing cell reference via another cell
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 "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. |
#3
|
|||
|
|||
changing cell reference via another cell
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 "chicolini" wrote in message ... 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. |
#4
|
|||
|
|||
changing cell reference via another cell
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 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. -- 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. |
Thread Tools | |
Display Modes | |
|
|