Hi all.
I would like to insert text from a named variable into a cell reference.
Background
I have created an array formula to determine the cell reference of the first cell that has a positive number in a specific row.
The array calculates the cell address for the first cell with a positive number, for example: $E$45.
I have defined a name for the cell with the array that delivers the above result. Variable name = "first_year"
Next, I would like to use the variable name "first_year" [value = $E$45] as a variable cell reference >in another spreadsheet< within a percentage change formula:
Example—trying to calculate: ($100,000 - $50,000) / $50,000 = 100%
FORMULA =('Consolidated Income'!K45-'Consolidated Income'!$E$45)/'Consolidated Income'!$E$45
Does not work: =('Consolidated Income'!K45-'Consolidated Income'!first_year)/'Consolidated Income'!first_year
... and of course, I can't figure out how to do it. But someone knows how.
Assistance appreciated. Thanks.
Bookmarks