0

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.

enter image description here

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.

enter image description here

Any recommendations would be appricated.

Thanks, Brad

Improve this question
9
  • 1
    I don't think you can wrap your whole formula with indirect. In fact, maybe it was last week, I found that you cannot indrect an array, so that may be compounding your problems
    – gns100
    Commented Nov 24, 2021 at 17:06
  • Thanks I thought I might be trying to accomplish something that you couldn't do. But, I thought i should reach out just case someone knew a way, or could confirm it couldn't be done.
    – BradR
    Commented Nov 24, 2021 at 17:42
  • Correcting myself, you can use indirect on an array. For example =SUM(INDIRECT("A1:A5")) works. Unfortunately indirect needs cell references, so A:A will not work. So moving your indirect to the point of the cell references AND figuring out the substitute for A:A should get you there.
    – gns100
    Commented Nov 24, 2021 at 18:29
  • To declutter this, maybe use the LET formula also...
    – gns100
    Commented Nov 24, 2021 at 18:29
  • Maybe I should just shut up. I just tried =SUM(INDIRECT("A:A")), and it does work...
    – gns100
    Commented Nov 24, 2021 at 18:37

1 Answer 1

Reset to default
1

Change this portion of the second formula (the unsuccessful one):

MAX(("'"&A9&"  &  '!"&  A:A  <>  ""  "")

(noting the areas I've stretched out with spaces) to this:

MAX((INDIRECT("'"&A9&"'!"&"A:A")<>"")

Presently there are several sins in this stretch. First is you cannot simply build the string and use it directly as an address. That is the use of INDIRECT(), to take such and do such. You clearly know this, so must have just forgotten the step when making it out.

The second sin is the extra ampersand (&) in the bit of string after "A9"... no bueno. Gotta remove that.

The third is that there are no doublequotes around the A:A portion. It must have them.

Fourth and final is the string of four doublequotes on the other side of the comparison. It needs to be TWO of them, not four.

Once you do those things, it works like a charm.

Improve this answer
2
  • Thanks, I tried several renditions and was loosing faith. The final version get the last row with data referencing the cell with the worksheet name (A9) = SUMPRODUCT(MAX((INDIRECT("'"&A9&"'!"&"A:A")<>"")*ROW(INDIRECT("'"&A9&"'!"&"A:A")))))
    – BradR
    Commented Nov 29, 2021 at 19:25
  • Of course to get add the column this needs to go in front INDIRECT("'"&A9&"'!"&"A"&
    – BradR
    Commented Nov 29, 2021 at 19:27

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .