2

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

Pivot Table

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?

Ian Brigmann
  • 79
  • 8
  • 1
    try it without the quotes around D1 – Scott Craner Feb 21 '17 at 18:26
  • 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 Feb 21 '17 at 18:30
  • ...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 Feb 21 '17 at 18:42
  • @BrandonBarney - The [VLOOKUP function](https://support.office.com/en-us/article/vlookup-function-adceda66-30de-4f26-923b-7257939faa65) needs [INDIRECT](https://support.office.com/en-us/article/indirect-function-21f8bcfc-b174-4a50-9dc6-4dfb5b3361cd) if D1 contains a string defining the *table_array* parameter. Without it, VLOOKUP will simply look in D1, not the table_array defined within D1 and since D1 is a single cell, you cannot get the 2nd column out of it even if there was a match. –  Feb 21 '17 at 18:44
  • 1
    @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). –  Feb 21 '17 at 18:47
  • Yep the indirect is used to construct the filepath for the external workbook. I used =VLOOKUP(A2,INDIRECT(D1),2,FALSE) so without the quotes and only a #REF! error is given. I just can't seem to find why it doesn't work, ive used similar codes in the past. – Ian Brigmann Feb 21 '17 at 18:52
  • If i just copy and paste the fully constructed code (first set of brackets i gave) for the table array field it works perfectly. Thought some fresh sets of eyes might help to find any obvious problems. – Ian Brigmann Feb 21 '17 at 18:56
  • 1
    My earlier comment was in error. I've tried with INDIRECT and get `#REF!` as well. Now trying with INDEX. –  Feb 21 '17 at 18:57
  • index then matching function seems to work with the given code. I guess i just don't understand the vlookup error lol It just kind of renders the endeavor useless since it requires opening 14 files concurrently to grab values – Ian Brigmann Feb 21 '17 at 19:09

1 Answers1

1

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.