If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

 
Go Back  dBforums > PC based Database Applications > Microsoft Excel > Using variable file names in a vLookup

Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old
Registered User
 
Join Date: May 2011
Posts: 47
Using variable file names in a vLookup

Does anyone have an example of using a variable file name (and tab) in a vLookup? I have many files with many vLookups that have to be changed every month. It would be wonderful if I could only key the variable name once instead of copying and pasting so many vLookups. I've tried a number of different ways to accomplish this but have been unsuccessful. Please, if anyone has an example that works?
Jack Kent
Reply With Quote
  #2 (permalink)  
Old
(Making Your Life Easy)
 
Join Date: Feb 2004
Location: New Zealand
Posts: 1,275
That got the gary matter thinking

trick use
=INDIRECT()

and build the a cell up and them point the INDIRECT to it and it should work
__________________
hope this help

See clear as mud


StePhan McKillen
the aim is store once, not store multiple times
Remember... Optimize 'til you die!
Progaming environment:
Access based on my own environment: DAO3.6/A97/A2000/A2003
VB based on my own environment: vb6 sp5
ASP based on my own environment: 5.6
VB-NET based on my own environment started 2007
SQL-2005 based on my own environment started 2008
MYLE

Last edited by myle; 12-04-12 at 03:47. Reason: spelling
Reply With Quote
  #3 (permalink)  
Old
Registered User
 
Join Date: Jan 2009
Location: Louisiana, United States
Posts: 163
Here's an example of the indirect function that Myle mentioned.

Joshua
Attached Files
File Type: zip Excel - Dynamic Lookup Example.zip (8.7 KB, 31 views)
Reply With Quote
  #4 (permalink)  
Old
Registered User
 
Join Date: May 2011
Posts: 47
Using variable file names in a vLookup

Thank you both. I got this to work for one of the arguments but not the other. It works great with:

=VLOOKUP(B3,INDIRECT("'["&C16&".xlsx]"&D16&"'!$B$1:$D$5"),[file2.xlsx]Sheet1!$D$1,FALSE)

but if I use the same format on the third argument:

=VLOOKUP(B3,INDIRECT("'["&C16&".xlsx]"&D16&"'!$B$1:$D$5"),"'["&C16&".xlsx]"&D16&"'!$B$1,FALSE")

I get a #Value error message.

Any thoughts would be appreciated.

Jack Kent
Reply With Quote
  #5 (permalink)  
Old
Registered User
 
Join Date: Jan 2009
Location: Louisiana, United States
Posts: 163
It could be a number of things. I'd check in the following order:

1. Check workbook name to see that it is the same as C16 with neither having trailing spaces. Make sure it is an .xlsx and not an .xls

2. Check the worksheet/tab for the same along with D16

3. Other than that, I'd check your quote marks and concatenation. Excel can be finicky with that stuff.
Reply With Quote
Reply

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On