Indirect and Vlookup Reference Errors

Question

Ive constructed the following path with the code below:

'I:\Capital Markets\Post Closing\Audit\Team Reports\Audit Time\2017\February\[feb12017.xlsm]TimeInOffice'!A:C

Using this code

=("'I:\Capital Markets\Post Closing\Audit\Team Reports\Audit Time\" & TEXT(Sheet2!$B$2,"yyyy") & "\" & TEXT(Sheet2!$B$2,"mmmmmmmmmmmmmmmmm") & "\" & "[" & AuditorCalcSheet!$CB4 & ".xlsm]TimeInOffice'!A:C")

AuditorCalcSheet!$CB4 refers to this pivot table that identifies the unique dates and provides the filename information for vlookup. $B$2 = a cell from the inserted data that contains the year and month A:C is the table range we are looking up

It successfully constructs the code but when I attempt to insert it into a vlookup function:

=VLOOKUP(A2,INDIRECT("D1"),2,FALSE)

A2 = a name D1 = the constructed filepath

The formula only gives either a #N/A error or a ref error, ive tried every variation i can think of and I seem to be glazing over the answer. Is there any other way to convey text from another cell into a formula if indirect doesn't work?

Try using it without Indirect. You should be able to use the cell as a direct reference that will return the string. I haven't had to use this with VLOOKUPS, but I have done it numerous times with GETPIVOTDATA on OLAP cubes. — Brandon Barney
...where's your use of Indirect()? Did you just not include it in the "Using this code" part? Also, just to note, you have to have the other workbook/worksheet open when using Indirect(). — BruceWayne
@BruceWayne - The INDIRECT is used within the active workbook to get the name of the external workbook into VLOOKUP, not as an external reference in itself. Haven't tested thjs but it looks like it should work (without the quotes). — user4039065
My earlier comment was in error. I've tried with INDIRECT and get #REF! as well. Now trying with INDEX. — user4039065

Unknown Unknown · Accepted Answer · 2017-02-21T19:19:14

This cannot be done. The INDIRECT function will not work on closed external references. You need to change the way Excel looks at the path, worksheet and columns from a text string to a real cell(s) address to use in the VLOOKUP function's table_array parameter.

If you open the workbook into the same applications space, the formula works¹ as one of these.

=VLOOKUP(A6,INDIRECT(D1),2,FALSE)
=VLOOKUP(A4, INDIRECT(TEXT(D1, "@")), 2, FALSE)
=VLOOKUP(A3, INDIRECT(INDEX(D1, 1, 1)), 2, FALSE)

¹ _{Your path as shown is incorrect. Because Excel uses a prefacing tick (e.g. single quote or ') to force text, you need to start your string with two ticks (e.g. ''I:\Capital Markets\Post Closing\Audit\Team Reports\Audit Time\2017\February\[feb12017.xlsm]TimeInOffice'!A:C) in order to retrieve the same string starting with one tick.}

Indirect and Vlookup Reference Errors

1 Answers