Variable Workbook name in formula?

J

Jed Shields

Active Member
Joined
Sep 7, 2011
Messages
283
Office Version
  1. 365
Platform
  1. Windows
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.
 

Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.
Sort by date Sort by votes
Try INDIRECT. I think it should be

=vlookup(A2,INDIRECT("'D:\[Database"&B1&"]Sheet1'!A:C"),2,FALSE)
 
Upvote 0
Ahhh that looks familiar. I'll check it out when I'm back at the office tomorrow :)
 
Upvote 0
I can't quite get the syntax right. Here's what I've got:

Code: 
[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]
 
Upvote 0
I think you are missing a "

icRange"&" "&B$1&".xls]RankMonthGUDMonth'!$A:$C"
 
Upvote 0
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 :)
 
Upvote 0
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?
 
Upvote 0
You must log in or register to reply here.

Similar threads

R
  • Question Question
Excel Database and Dependant Lists
Replies
7
Views
267
felixstraube
F
D
  • Solved
Want to use VLOOKUP and IF for a code from another workbook
2
Replies
11
Views
729
dudeinaghillie
D
N
  • Question Question
VBA to add title from formula to a save as name
Replies
0
Views
138
NKUNZI
N
M
  • Solved
Look if values exist seperately and together, then write Yes or No in cell
2
Replies
11
Views
795
MasterBash
M
S
  • Question Question
Workbook Index of Sheet name Hyperlinks + additional information
Replies
1
Views
334
stuartgb100
S

Forum statistics

Threads
1,224,599
Messages
6,179,827
Members
452,946
Latest member
JoseDavid

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top