Using a cell reference to specify a worksheet within a file

micpl30

New Member
Joined
Oct 19, 2006
Messages
5
I'm new here, hoping that some expert out there can help me out.

I'm working with the following vlookup function:

=VLOOKUP($E$2,'[EXCELFILENAME.xls]Oct'!$A$12:$F$20,2)

But, instead of always looking at the worksheet Oct, I want to enter the name in another cell, and have the formula use whatever I enter.

For example, if I entered Dec, the function would go out and do the vlookup command in that worksheet.

The cell I enter the month in is $D$2. I thought it would be something like:

=VLOOKUP($E$2,'[EXCELFILENAME.xls]$D$2!$A$12:$F$20,2)

But, that doesn't work. It's looking for a worksheet called "$D$2", and not a worksheet that's the contents of cell $D$2, which is Oct, Dec, or whatever I enter.

Any and all comments are greatly appreciated.

Thanks,

Keith
Detroit, Michigan USA
Go Tigers!
 

Some videos you may like

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.

erik.van.geit

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832
Hi, micpl30
Welcome to the Board !!!!!

have a look at INDIRECT in the helpfiles
try (untested, but will be close)
=VLOOKUP($E$2,INDIRECT("[EXCELFILENAME.xls]" & A1 & "!$A$12:$F$20"),2)

assuming sheetname is in A1 of current sheet

kind regards,
Erik
 

Richard Schollar

MrExcel MVP
Joined
Apr 19, 2005
Messages
23,707
Hi Keith

Welcome to the Board!

You can do this by using INDIRECT to build the reference, but the limitation with indirect is you have to have the target workbook open at the same time (you can download the morefunc add-in (see thread Recommended Links and Downloads) to utilise INDIRECT.EXT to get around this limitation.

Anyway, the formula would be:

Code:
=VLOOKUP($E$2,INDIRECT("'[EXCELFILENAME.xls]" & $D$2 & "'!$A$12:$F$20"),2)

Be careful to copy the above formula precisely, as there are double quotes and single quotes together (ie "').

Hope this helps!
 

micpl30

New Member
Joined
Oct 19, 2006
Messages
5

ADVERTISEMENT

Wow ! That works great!

Thanks so much, Richard's suggestion works. One quick followup question for you guys.

=VLOOKUP($E$2,INDIRECT("'[EXCELFILENAME.xls]" & $D$2 & "'!$A$12:$F$20"),2)

What's the function of the quote and double quote? I looked in the help file for indirect, and it looks like the double quote might exempt whatever's enclosed in quotes from the indirect function. I think that's right.

If that's the case, I followed what Erik was trying to do in his suggestion: (edited slightly for the right cell reference)

=VLOOKUP($E$2,INDIRECT("[EXCELFILENAME.xls]" & $D$2 & "!$A$12:$F$20"),2)

But, that doesn't work. The single quotes seem to be key. Erik mentioned in his followup that the single quotes were required when the filenames or sheets had spaces. The filename I'm using doesn't have any spaces in it, nor does the sheet, but it doesn't work without the single quotes.

Is that right, the double quotes exempt what's enclosed? How about the single quotes, and also, what's the ampersand & for? I tried entering these into excel help, but nothing comes up.

I'm going to try that morefunc add-in suggestion also, because ideally I'd like to not have all the files open.

You guys are a great help, thanks so much for taking the time.

Thanks,

Keith
 

erik.van.geit

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832
it works for me without single quotes when filenames and sheetnames don't have spaces: can't help you there, but will always use single quotes from now on
(I think I've said that once before :oops: )

ampersand is used to put strings together (CONCATENATE)

check out
A1 has "bla"
B1 has "ck"
C1 formula =A1&B1

result "black"
 

micpl30

New Member
Joined
Oct 19, 2006
Messages
5

ADVERTISEMENT

not working right

This was working great, until I changed the $D$2 variable to reference a different page. The formulas now calculate as a number, like 39034, 39035, etc.

If I open the files that are being referenced, it works fine. If they're not open, I get the number result.

Any thoughts? Thanks!

=VLOOKUP($E$2,INDIRECT.EXT("'[68DN 4015 LY7_Auto_AWD_FE1_IVER_RH.xls]" & $D$2 & "'!$A$12:$F$20"),2)
 

micpl30

New Member
Joined
Oct 19, 2006
Messages
5
what's really weird is that if the cell being looked up isn't in the first row of the lookup table, it works without the file being opened.
 

Richard Schollar

MrExcel MVP
Joined
Apr 19, 2005
Messages
23,707
Hi

Those return values look like dates - format the Vllokup cells (ie the ones with the vlookup formula) as a date - then you should see the right format.
 

micpl30

New Member
Joined
Oct 19, 2006
Messages
5
Yes, it turns out they are dates, which is the data from the next row in the table, not the one I'm looking up.

This whole thing works fine if:

a) I'm doing the vlookup on anything other than the first row of data in the table

or

b) I have the file I'm doing the vlookup on already open.


Weird. I'm thinking it's some kind of error in the function. Any thoughts?
 

Watch MrExcel Video

Forum statistics

Threads
1,114,375
Messages
5,547,575
Members
410,798
Latest member
Candyman8019
Top