Using Concatenate to populate the reference of an Offset

[tiredExcelUser]

Hello,

I am trying to build an output in my model where a user selects a tab name from a dropdown in cell A1 and cell address in cell A2 and the offset formulas automatically update. I thought this formula would work but it doesn't

=offset(CONCATENATE("'"&A1&"'!"&A2),1,0)

any help would be greatly appreciated.

 

[Peter_SSs]

Is this what you mean? If not, please give use a small set of sample data and the expected result with XL2BB

=OFFSET(INDIRECT("'"&A1&"'!"&A2),1,0)

 

[Joe4]

Two things:
1. You do NOT need to use both CONCATENATE and &, as they both do the same thing (choose one or the other)
2. You need to use INDIRECT when build range references to turn them from a string into a range.

Try:

=OFFSET(INDIRECT("'"&A1&"'!"&A2),1,0)

EDIT: Too slow! Looks like Peter already posted the formula. I will leave my post though, for the comments I included which may aid in your learning.

 

[tiredExcelUser]

Thank you Peter and Joe, INDIRECT worked.

 

[Joe4]

You are welcome.
Glad we were able to help!