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.
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.