Help with Indirect Reference

[eggo]

I'm trying to do a vlookup (in a cell, not VBA) where the range is determined by a variable stored in another cell.

=VLOOKUP(TRIM(A3)&"*",'\\030-DC1\Company$\ADOE 30\re-entry calc\[ReEntry Calculations.xlsx]August 13'!$A$2:$N$150,6,0)

That is the lookup without the variable. It works great.

I made a new section of the local worksheet to create the path and fill in the sheet name:

O1 determines the local sheet name (in this case, "August 13")
N1 contains the path '\\030-DC1\Company$\ADOE 30\re-entry calc\[ReEntry Calculations.xlsx]
N2 Contains the range '!$A$2:$N$150
N3 sticks them all together =CONCATENATE(N1,O1,N2)
That is all working fine and N3 ends up with the value 
[I]'\\030-DC1\Company$\ADOE 30\re-entry calc\[ReEntry Calculations.xlsx]August 13'!$A$2:$N$150[/I]<table style="border-collapse: collapse; width: 48pt;" border="0" cellpadding="0" cellspacing="0" width="64"><tr style="height: 15pt;" height="20"><td class="xl63" style="height: 15pt; width: 48pt;" height="20" width="64">
</td></tr></table>

So it seems to me that this formula should work:

=VLOOKUP(TRIM(A2)&"*",[B]INDIRECT($N$3,1)[/B],6,0)

When I show the calculation steps, I can see it pulling the value, but it returns a #ref error when it evaluates the indirect() function.

If I change it to

=VLOOKUP(TRIM(A2)&"*",[B]$N$3[/B],6,0)

It doesn't pull the value from the cell, but tries to use N3 as the range.

Anyone see where I'm going wrong?

 

[Trebor76]

Hi eggo,

As you've "indirectly" found out (pun intended) the INDIRECT function only works with external references if that external reference (i.e. workbook) is open. If you really want to do this, you'll either need to use a customised User Defined Function (udf), specific code or some add-in which will slow performance down.

If you do want to try a customised VBA approach, see the third last reply from this thread by Zack Barresse for a great list of choices.

HTH

Robert

 

[eggo]

In my example N3 is in the local worksheet, so indirect($N$3) isn't referencing the other workbook. Is it? I would think the value would be treated just like a regular string (that happens to be a path) Is that not the case?

 

[Trebor76]

Does the source data (i.e. the 'August 13' tab) that the VLOOKUP function is using to "look up" reside in the active workbook you're working with?

 

[eggo]

Just to clarify, I'm not tied to using Indirect() for this. I thought Value($N$3) would work, but it gives me a type mismatch even though everything is formatted as text.

 

[eggo]

Does the source data (i.e. the 'August 13' tab) that the VLOOKUP function is using to "look up" reside in the active workbook you're working with?

no, but the Indirect() is just there to feed the path (with a different sheet name depending on the active sheet name) to the vlookup function. so vlookup() is referencing the external workbook, not Indirect(). At least that's the understanding I have.

 

[Trebor76]

If the INDIRECT function is used in any part of a formula where any part of that formula refers to a closed external reference (workbook) it will return #REF!

See this M$ article for more info.

 

[eggo]

OK, so is there an easy way to insert the contents of N3 into my vlookup() function?

 

[Trebor76]

No, as far as I know (and the board's open here for other suggestions) you'll need to use one of the work arounds that Zack Barresse has listed (see my first reply to your thread).

 

[Trebor76]

I suppose you could just do an edit / replace (Ctrl + H) for the specific part of the VLOOKUP function that needs to be changed from whatever value to whatever value (but be specific).