Question: I am having trouble referencing an indirect within an indirect.
Background:
I have additional Sheets (tabs) that will be modified on the fly and i need to be able to change 1 cell's value and have it update all cells referencing that tabs name or be able to add a new tab and have it now reference that new tabs name.
I have the following formula correctly working in order to reference a cell with a dynamic sheet/tab name.
=INDIRECT("'"&b5&"'!F141")
In addition i need to be able to consistenly grab the last row in a column on the fly for the newly added sheets/tabs. When the new sheets are added they may have different length columns. The formula i have to grab the last row in a sheet is:
=INDIRECT("'Sheet4'!F"&COUNTA('Sheet4'!F:F))
Problem: I need to be able to dynamically reference the sheet/tab at the same time I am dynamically referencing the last row in the new sheet/tab. Basically I need to combine my first two solutions together.
Failed Solutions: i have the following attempted combination that is not working.
=INDIRECT("INDIRECT("'"&b5&"'!b2")!F"&COUNTA(INDIRECT("'"&b5&"'!b2")!F:F))
Any thoughts on combining the two would be much appreciated.
Screen Shot 2016-10-23 at 4.00.08 PM.png
Bookmarks