SOLVED

Indirect formula

Copper Contributor

Hello,

 

The following formula searches the last cell with text in the sheet called '500 Amy' in column A.

=LOOKUP(2,1/('500 Amy'!$A:$A<>""),'500 Amy'!$A:$A)

 

I would like to replace '500 Amy' with some sort like an INDIRECT formula so that I can reference the cells in column A and B to drag down the column.

For example,

AB
500Amy
600Betty
700Catherine 

 

A formula that looks like this but it shows an error.

=LOOKUP(2,1/('INDIRECT(A1&""&B1'!$A:$A<>""),'INDIRECT(A1&""&B1'!$A:$A)

 

Thank you.

Queenie

1 Reply
best response confirmed by Queenie Lai (Copper Contributor)
Solution

INDIRECT for 

'500 Amy'!$A:$A

will be

INDIRECT("'" & A1 & " " & B1 & "'!$A:$:A")

 

 

1 best response

Accepted Solutions
best response confirmed by Queenie Lai (Copper Contributor)
Solution

INDIRECT for 

'500 Amy'!$A:$A

will be

INDIRECT("'" & A1 & " " & B1 & "'!$A:$:A")

 

 

View solution in original post