Find last row of specific text

[cab401]

Help! This formula works when finding the last row number containing “Smith” within the same sheet but can’t get a similar formula to work referencing another sheet.

Works:
=sumproduct(max((A30:A50=“Smith”)*row(A30:A50)))
Result: A42

Doesn’t work:
=sumproduct(max((indirect(C1&”!A30:A50”=“Smith”)*row(indirect(C1&”!A30:A50”)))))
Result: #VALUE!

C1 references another open worksheet & this works with several other formulas so I know it must be the syntax of the “indirect” version.

 

[Marcelo Branco]

Maybe...

=SUMPRODUCT(MAX((INDIRECT("'"&C1&"'!A30:A50")="Smith")*(ROW(INDIRECT("'"&C1&"'!A30:A50")))))

M.

 

[Fluff]

Beaten 2it

 

[Marcelo Branco]

oops.. unnecessary parentheses in the formula above

Simpler
=SUMPRODUCT(MAX((INDIRECT("'"&C1&"'!A30:A50")="Smith")*ROW(INDIRECT("'"&C1&"'!A30:A50"))))

M.

 

[cab401]

oops.. unnecessary parentheses in the formula above



Simpler
=SUMPRODUCT(MAX((INDIRECT("'"&C1&"'!A30:A50")="Smith")*ROW(INDIRECT("'"&C1&"'!A30:A50"))))

M.

This worked perfectly - THANK YOU!!

 

[Marcelo Branco]

You are welcome. Glad to help.

M.