Excel Gurus?

R

Ravyn

Veteran XV
So I'm using VLookup to draw some values from one workbook into another. However, I would like to specify the Table_Array for the Vlookup using a cell in another worksheet within the workbook.

So I have 2 files:
Report.xls
Numbers.xls

using Vlookup to pull from Numbers.xls into Report.xls I use:
vlookup("Sales",'[Numbers.xls]January!$A$1:$J$140,3,FALSE)

However, I need to be able to change Numbers.xls within that formula to whatever filename I so choose, that I can input into a cell within the Report.xls workbook.

So if I set Paths!A1 within Report.xls to Numbers2013.xls I want it to change the formula to:
vlookup("Sales",'[Numbers2013.xls]January!$A$1:$J$140,3,FALSE)

Tried lots of different ways like:
vlookup("Sales",'"["&A1&"]"January!$A$1:$J$140,3,FALSE)
without any luck so far.

Is that possible?
 
Example with B5 as Book1 or whatever the file name
A1=cell with the word sales in it

=VLOOKUP(A1,INDIRECT("'["&B5&"]January'!$A$1:$J$140"),3,false)

But this only works when the data file is open. Google indirect and closed workbooks for some macros to auto pull the data so you don't have to open every file you wish to lookup data from but this method overall may be more of a pain than an aid
 
Last edited:
I've been playing around with INDIRECT() now.. however I'm getting a REF error with this:

vlookup("Sales",INDIRECT("Paths!"&D1),etc,etc)

Can't seem to get INDIRECT to correctly pull the Data from a different worksheet within the same workbook..

If I put the proper string into a cell on the same worksheet and then use INDIRECT(D1) or whatever, the Vlookup works. But if I put that same value into D1 on a different worksheet (Paths for example), it gives me a REF error.
 
You're missing some apostrophes in that case I believe, around paths
 
Last edited:
make a user defined function with select case maybe?

e.g.

Select Case TargetWorksheet

Case January

worksheetfunction.vlookup(...Numbers.xls!January...)


also, try using full filepaths in your strings
 
You must log in or register to reply here.
Back
Top