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!
 

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand
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
 
Upvote 0
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!
 
Upvote 0
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
 
Upvote 0
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"
 
Upvote 0
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)
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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?
 
Upvote 0

Forum statistics

Threads
1,213,506
Messages
6,114,027
Members
448,543
Latest member
MartinLarkin

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