using indirect in vlookup to insert search range

[smakatura]

I have a range ['[Aspect Inbound Stats (All).xlsx]BE Hourly'!$A:$ah . I have this value in a cell (cell O9).

I want to use this range in a vlookup. the tab (BE Hourly) will change with each row to whatever is on the new row- but sheet name and column references stays the same. however this will change values as I move through the sheet so need to reference the cell or somehow just indirect the concatenate of the reference.

I have tried indirect function but get "variable" and it does not seem to reference it. I have the sheet is referencing open already so that is not the issue.


I have tried the following with no luck
VLOOKUP(B30,INDIRECT(CONCATENATE("'[Aspect Inbound Stats (All).xlsx]",D30,"'!$A:$ah")),4,FALSE)
VLOOKUP(B30,INDIRECT(O9),4,FALSE)
VLOOKUP(B30,O9,4,FALSE)

I feel like I am missing something simple

this formula will be put in the cell by VBA, so if there is a better option through vba I am open to it

[Special-K]

Should it be this, single quote after the filename?

VLOOKUP(B30,INDIRECT(CONCATENATE("'[Aspect Inbound Stats (All).xlsx]'",D30,"!$A:$AH")),4,FALSE)

or


VLOOKUP(B30,INDIRECT("'[Aspect Inbound Stats (All).xlsx]'"&D30&"!$A:$AH")),4,FALSE)