Excel formula to define the last non-empty cell in a column in a sheet chosen by the user

0
votes

I have an Excel file with several sheets with data and I have a "main" sheet called summary. To explain let say that in my workbook I have the four sheets : "summary", "apple", "orange" and "lemon". I organized the summary sheet so that by settings the value of cell B2 I can get the data from the other sheets. So if B2 = orange I have some data from the sheets "orange" and if I set B2 = lemon I get the same data from the sheet lemon.

What I am not able to do is to define a formula to get the last non empty cell in the selected sheet.

I found this formula in this forum to select the last non empty formula in a column:

=MAX((A:A<>"")*(ROW(A:A)))

What I would like to do is to edit this formula so that if

B2 = Orange

the formula becomes

=MAX((Orange!A:A<>"")*(ROW(Orange!A:A)))

and if 

B2 = Apple

the formula becomes

=MAX((Apple!A:A<>"")*(ROW(Apple!A:A)))

I tried editing the formula by using INDIRECT

=MAX((INDIRECT("'"&$B$2&"'!A:A")<>"""")*(ROW(INDIRECT("'"&$B$2&"'!A:A"))))

but the result I get is always 1048576 (the last admissible row in my excel).

Can you please help me?

1
excelexcel-formula

1 Answers

2
votes

If the original Array Formula was :

=MAX((junk!A:A<>"")*ROW(junk!A:A))

and cell B1 contained:

junk

then an equivalent Array Formula would be:

=MAX((INDIRECT(B1&"!"&"A:A")<>"")*(ROW(INDIRECT(B1&"!"&"A:A"))))

Array formulas must be entered with Ctrl + Shift + Enter rather than just the Enter key.