Combo Box Value with VLOOKUP

[Jean9]

So, I've created a named combo box control in a MS Excel 2007 workbook containing file names that are stored on another worksheet (DriveFiles) in the same workbook. I've set the combo box cell link to D1 on my worksheet and set E1 =IF(D1="","","S:\Accounting\Financials\Linked Spreadsheets\[" & INDEX(DriveFiles!A:A,D1,0)) &"]" on my worksheet.

I want to use the value in E1 as part of a VLOOKUP function in various other cells like so:
=VLOOKUP(B9,"'" & E1 & "Cons by month'!$B$8:$O$199",$K$4,0) but am getting #VALUE! in the cell for this formula. K$4's formula is =VLOOKUP(D3,$L$4:$M$15,2,0).

The intent is to allow the user to select a file name from the dropdown and the data in the cells vary to display the data from the selected file.

This formula works when it looks like this:
=VLOOKUP(B8,'S:\Accounting\Financials\Linked Spreadsheets\[Consolidated 2011 Forecast 1+11.xlsx]Cons by month'!$B$8:$O$199,$K$4,0) - ie. the path is hardcoded into the formula

E1's value is = S:\Accounting\Financials\Linked Spreadsheets\[Consolidated 2011 Forecast 1+11.xlsx] but =VLOOKUP(B9,"'" & E1 & "Cons by month'!$B$8:$O$199",$K$4,0) doesn't work.

What I be missing to make this work?

Thanks in advance for any help.

 

[SkipVought]

hi,

When you have a STRING that is part of REFERENCE, you cannot simply concatenate two or more STRINGS together and the 'declare' or 'hope' that a STRING becomes a REFERENCE.

This is what the INDIRECT function does: it converts a STRING to a REFERENCE. So where your lookup range is, enclise that in the INDIRECT function.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Jean9]

Thanks, Skip
The use of
=VLOOKUP(B8,"'" & INDIRECT(E1) & "Cons by month'!$B$8:$O$199",$K$4,0)
in the cell results in a #REF! error even with the file open. However, the user will not have the other workbook open when they use this function.
The hardcoded path formula (=VLOOKUP(B9,'S:\Accounting\Financials\Linked Spreadsheets\[Consolidated 2011 Forecast 1+11.xlsx]Cons by month'!$B$8:$O$199,$K$4,0) allows for the other workbook to be closed.

How should the syntax read for the referenced workbook in the VLOOKUP to be closed yet the data still accessible?

 

[SkipVought]

[highlight]THIS[/highlight] is a STRING!!!
[tt]
=VLOOKUP(B8,"'" & INDIRECT(E1) [highlight]& "Cons by month'!$B$8:$O$199"[/highlight],$K$4,0)
[/tt]
You might want
[tt]
=VLOOKUP(B8,"'" & INDIRECT(E1 & "Cons by month'!$B$8:$O$199"),$K$4,0)
[/tt]
where I assume that E1 is a reference to the PATH STRING.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Jean9]

I've tried:
=VLOOKUP(B8,"'" & INDIRECT(E1 & "Cons by month'!$B$8:$O$199"),$K$4,0) and
=VLOOKUP(B8,INDIRECT("'" & E1 & "Cons by month'!$B$8:$O$199"),$K$4,0)
both result in a #REF error and this is while that workbook that the VLOOKUP is referencing is opened.

E1 is only going to contain the folder path and file name and extension. The cell reference (ie Cons by month'!$B$8:$O$199) is going to be maintained as a literal in the VLOOKUP function for the cell.

 

[SkipVought]

What is the value in E1?

Also select everything inside the INDIRECT and hit F9. The expression will be evaluated. Is this what you expect? Hit ESC to exit

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Jean9]

The value in E1 is S:\Accounting\Financials\Linked Spreadsheets\[Consolidated 2012 Forecast 1+11.xlsx]

After hitting F9 on formula =VLOOKUP(B8,INDIRECT("'" & E1 & "Cons by month'!$B$8:$O$199"),$K$4,0), I see:
=VLOOKUP(B8,INDIRECT("'S:\Accounting\Financials\Linked Spreadsheets\[Consolidated 2012 Forecast 1+11.xlsx]Cons by month'!$B$8:$O$199"),$K$4,0)

This statement is what I would expect to see. However, I get a #REF! error.

The formula with the literal file address works:
=VLOOKUP(B8,'S:\Accounting\Financials\Linked Spreadsheets\[Consolidated 2012 Forecast 1+11.xlsx]Cons by month'!$B$8:$O$199,$K$4,0)

With the exception of the INDIRECT function and the double quotes around the file path, these two look the same to me....