Forum Discussion
Dynamic Cell Address Help
Hello, I'm having some trouble and I'm hoping the kind people here can help. I'm using the ADDRESS function to generate the cell address of a cell in a separate sheet. I have the sheet name (Shee...
The problem with the way you're trying to do it is that your formula in cell R4 is concatenating text values, which aren't dealt with dynamically like actual cell references.
It would help people make suggestions, I suspect, if you could describe the underlying (or "overarching") purpose. Why are you trying to do this in the first place? It is entirely possible, if we knew your ultimate goal, there is a far simpler solution. So if you could give an idea of WHY that cell in that separate sheet is being referenced in the first place--are you just looking for the value in it? Something else?
I'm wondering why (to let you know why I'm asking) .. why you can't just refer to the cell? I have a spreadsheet that refers to cells in two other spreadsheets, and does it simply by the formula =[OtherSheet.xls]Worksheet1!$A$6 In that formula you could keep or remove the absolute reference aspect....
It would help people make suggestions, I suspect, if you could describe the underlying (or "overarching") purpose. Why are you trying to do this in the first place? It is entirely possible, if we knew your ultimate goal, there is a far simpler solution. So if you could give an idea of WHY that cell in that separate sheet is being referenced in the first place--are you just looking for the value in it? Something else?
I'm wondering why (to let you know why I'm asking) .. why you can't just refer to the cell? I have a spreadsheet that refers to cells in two other spreadsheets, and does it simply by the formula =[OtherSheet.xls]Worksheet1!$A$6 In that formula you could keep or remove the absolute reference aspect....
It's a little complicated but I'll try my best...
So in Book1 I have a tab labeled "TAB 1"
Also in Book1 is a tab to take certain values from the tab labeled "TAB 1" and put them in a specific format to export that tab into a CSV file (TAB A).
In TAB A is a table, with row C4 manually assigned the number corresponding to the TAB number, designed so that any number of TABs can be created in Book1. The whole idea is to make it so that no matter what I change in "TAB 1", my table in TAB A will always populate with the same reference cell no matter where it is moved in case extra rows are inserted above it or whatever else in TAB 1.
Cells R4:AC4 in TAB A need to contain the dynamic cell address of cells C12:N12 in TAB 1.
Cell Q4 in TAB A contains =CONCATENATE("TAB ",C4)
Cells D4:O4 in TAB A currently contain =INDIRECT("'"&$Q4&"'!"&R4) so that D4 will then in effect display ='TAB 1'!C12.
I have set it up this way, or am trying to, so that no matter what the TAB number is, where cells C12:N12 in the respective tab end up being moved, or whatever else might change, the table in TAB A will always populate with the correct cell - in this case, 'TAB 1'!C12. And if i were to insert a row in TAB 1 above row 12, making my reference cell now C13, my reference value in TAB A would change to C13 as well instead of staying on 'TAB 1'!C12.
I know this is very confusing and I'm sure there's a much simpler way to do it, but this is what I'm stuck with at the moment.
So in Book1 I have a tab labeled "TAB 1"
Also in Book1 is a tab to take certain values from the tab labeled "TAB 1" and put them in a specific format to export that tab into a CSV file (TAB A).
In TAB A is a table, with row C4 manually assigned the number corresponding to the TAB number, designed so that any number of TABs can be created in Book1. The whole idea is to make it so that no matter what I change in "TAB 1", my table in TAB A will always populate with the same reference cell no matter where it is moved in case extra rows are inserted above it or whatever else in TAB 1.
Cells R4:AC4 in TAB A need to contain the dynamic cell address of cells C12:N12 in TAB 1.
Cell Q4 in TAB A contains =CONCATENATE("TAB ",C4)
Cells D4:O4 in TAB A currently contain =INDIRECT("'"&$Q4&"'!"&R4) so that D4 will then in effect display ='TAB 1'!C12.
I have set it up this way, or am trying to, so that no matter what the TAB number is, where cells C12:N12 in the respective tab end up being moved, or whatever else might change, the table in TAB A will always populate with the correct cell - in this case, 'TAB 1'!C12. And if i were to insert a row in TAB 1 above row 12, making my reference cell now C13, my reference value in TAB A would change to C13 as well instead of staying on 'TAB 1'!C12.
I know this is very confusing and I'm sure there's a much simpler way to do it, but this is what I'm stuck with at the moment.
- Mike -- is there a way to upload a sample of the workbook(s) you're dealing with. It's next to impossible to visualize what you're describing. Or if what you're dealing with contains confidential info of some kind, perhaps some simpler example with innocuous data in cells....
Without knowing more, it sounds like something that Excel's database functions might help resolve. I say this based on the desire to move "certain values from the tab labeled "TAB 1" and put them in a specific format to export that tab into a CSV file (TAB A)" .... maybe not, but most people are unaware of those functions altogether. They all begin with D____. Look at DSUM, DGET, and so forth.
It also doesn't sound like you're dealing with what one might call "standard spreadsheet" applications--i.e., not numbers per se in rows and columns, but rather information of some other kind, and need to extract meaningful reports or extracts. That sounds to me like "database" .... so the tools designed for that might be suited. But without a more full description of your context, it's hard to say.