Variable Workbook name in formula?

[Jed Shields]

Hi guys,

I've got a row of simple vlookup formulae looking at a seperate workbooks. Instead of having each formula differing by the name of the workbook eg:

D:\Database 201101Jan, D:\Database201102Feb etc

I'd like to be able to have a standard formula and reference the specific name difference based on column headings eg:

201101Jan, 201102Feb etc

If 201101Jan was in B1, 201102Feb was in C1 I want the formula to be something like this:

B2: vlookup(A2,'D:\[Database&B1]Sheet1'!A:C,2,FALSE)
C2: vlookup(A2,'D:\[Database&C1]Sheet1'!A:C,2,FALSE)

This obviously doesn't work otherwise I wouldn't be posting Any ideas on the correct syntax?

Cheers,
Jed.

 

[VoG]

Try INDIRECT. I think it should be

=vlookup(A2,INDIRECT("'D:\[Database"&B1&"]Sheet1'!A:C"),2,FALSE)

 

[Jed Shields]

Ahhh that looks familiar. I'll check it out when I'm back at the office tomorrow

 

[Jed Shields]

I can't quite get the syntax right. Here's what I've got:

[FONT=Arial][SIZE=2]=VLOOKUP($A2,INDIRECT("'T:\PURCH\Ops  Dashboards\Orange Dashboard\Test\[OrangeDatabaseDynamicRange&"  "&B$1&".xls]RankMonthGUDMonth'!$A:$C"),3,0)


Where:

Path = T:\PURCH\Ops Dashboards\Orange Dashboard\Test

Filename = OrangeDatabaseDynamicRange 201101 Jan,
                 [/SIZE][/FONT][FONT=Arial][SIZE=2]OrangeDatabaseDynamicRange 201102 Feb etc.

Sheet = RankMonthGUDMonth
[/SIZE][/FONT]

 

[VoG]

I think you are missing a "

icRange"&" "&B$1&".xls]RankMonthGUDMonth'!$A:$C"

 

[Jed Shields]

Actually, in the formula I posted you're right, but it was included in my spreadsheet and still giving an error. However, after rewriting it a couple of times it's now working. I'm not entirely sure what was wrong

 

[Jed Shields]

Just noticed a flaw in this - it looks like it will only work if the additional workbooks are open. Is there any way around this?

 

[VoG]

Sorry, no. INDIRECT will not work with closed workbooks.