Good Day,
Does anyone have recommendations on how to reference a cell with the worksheet name in it and add it to a formula that determines the last row with data in it on that worksheet.
I've used two different formulas Cells M9 & M10 with formulas directly referring to a worksheet named 8 that tells me the last cell in column A with data is in row 15. That is the expected result.
=SUMPRODUCT(MAX(('8'!A:A<>"")*ROW('8'!A:A))) or =MAX(IF(ISBLANK('8'!A:A),0,ROW(A:A))) will provide that.
The screen cap below shows these formulas providing the correct results cells M9 & M10 with the related formulas shows in column N.
Since this is the main page is a table of contents I would like the sheet name to look at the related cell in column A and pull populate the sheet name. That way I can pull the formula down for any new changes. This works with the column in the formula below, but I can't figure out how to use indirect or similar to add it to the formulas above. Bolded below.
=INDIRECT("'"&A9&"'!" &"A"&SUMPRODUCT(MAX(('8'!A:A<>"")*ROW('8'!A:A))))
In the next formula I tried to have INDIRECT get the start of the row search formula, but it does not provide the correct results. Sorry I can't seem to get that component to bold correctly.
=INDIRECT("'"&A9&"'!"&"A"&SUMPRODUCT(MAX(("'"&A9&"&'!"&A:A<>"""")*ROW('8'!A:A))))
The screen cap below shows the correct results in cell K8, but the issue in K9.
Any recommendations would be appricated.
Thanks, Brad
indirect
. In fact, maybe it was last week, I found that you cannot indrect an array, so that may be compounding your problemsindirect
on an array. For example=SUM(INDIRECT("A1:A5"))
works. Unfortunately indirect needs cell references, soA:A
will not work. So moving your indirect to the point of the cell references AND figuring out the substitute forA:A
should get you there.LET
formula also...=SUM(INDIRECT("A:A"))
, and it does work...