Referencing outside Tabs with reference formulas

[NZ Kiwi]

I’ve hit a brick wall and hope someone can help. I have developed a worksheet that references multiple pivot tables, which in turn have multiple tabs. These tabs are Region 1, Region 2, Region 3, etc.

My current formula reads like this:

HLOOKUP(F$4,'C:\Monthly Reports\[Sales Rprt - Freq.xls]Region 1'!$A$4:$CZ$9,3,FALSE)

where the lookup is going to the specified worksheet and tab. What I want to do instead is have the formula reference Region 1 on the current worksheet in field B2. I can then change B2 from Region 1 to Region 2 and so on and the lookup formula changes automatically. I’ve tried changing the formula from saying Region 1 to +B2 (see below)and trying to use the indirect formula, but have not been successful.

HLOOKUP(F$4,'C:\Monthly Reports\[Sales Rprt - Freq.xls]+B2'!$A$4:$CZ$9,3,FALSE)

I hope you can help. Thanks.

Doug

 

[just_jon]

Welcome to MrExcel -- you need the INDIRECT function which, unfortunately, works only with open workbooks.

Will the target book be open?

EDIT - Read this link for good info.

 

[NZ Kiwi]

If it needs to be open I'll open it.

 

[just_jon]

In that case try --

HLOOKUP(F$4,INDIRECT("'C:\Monthly Reports\[Sales Rprt - Freq.xls]"&B2&"'!$A$4:$CZ$9"),3,FALSE)

 

[NZ Kiwi]

Thanks for the formula Jon, it did the trick and will make my life much easier.

I have tried to replicate the INDIRECT on my INDEX formula here:

=INDEX(INDIRECT("'C:\ Monthly Reports\[AMH Monthly Charts - Depts.xls]"&$A$1&"'!$A$4:$CZ$97"),MATCH($A25,INDIRECT("'C:\Monthly Reports\[AMH Monthly Charts - Depts.xls]"&$A$1&"'!$A$4:$A$97"),0),MATCH(G$5,INDIRECT("'C:\ Monthly Reports\[AMH Monthly Charts - Depts.xls]"&$A$1&"'!$A$4:$CZ$4"),0))

Unfortunately I am getting the #REF! Error. Any suggestions?

Thanks a million!!

 

[just_jon]

What's in A1?

File is open, right?

 

[NZ Kiwi]

Sorry, A1 is the same as B2 above. It's the Region 1, Region 2 source that I will be changing and the formula needs to track.

Yes, the target file is open.

Doug

 

[just_jon]

A25/G5 are finding matches?

=MATCH($A25,INDIRECT("'C:\Monthly Reports\[AMH Monthly Charts - Depts.xls]"&$A$1&"'!$A$4:$A$97"),0)

returns a number from above?