Strings in formulas for cell references

[Peteor]

I am trying to make the following work in Excel, not VBA.

=INDEX('[&A2&.xlsm]&B2&'!$B:$G,5,1)

I know I am missing string quotations, but am unsure what else is missing. Does anyone have any input here?

 

[Fluff]

You need to use INDIRECT to do that.

 

[Peteor]

I am a bit confused on the usage of this function. I see the Indirect function is used to return a valid cell reference from a text string. I attempted to no avail:

=INDEX('[Indirect(A2).xlsm]Indirect(B2)'!$B:$G,5,1)

What would you change here?

 

[Fluff]

Try
=INDEX(INDIRECT("'["&A2&".xlsm]"&B2&"'!$B:$G"),5,1)