VLOOKUP formula question

keithcomic

New Member
Joined
Jul 10, 2008
Messages
4
Is there any way to create a VLOOKUP formula where a reference in the formula will change based on the contents of a certain cell? The formula needs to change.

For example: I create labor reports for my company. Each labor report is labeled as such

LABOR REPORT 20080710

The next days report will have that days date at the end.

For example:

LABOR REPORT 20080711

I want to create a formula that looks up a certain range based on a cell containing the title of the sheet.

I would want to enter 20080711 in a cell in a separate sheet and have the VLOOKUP formula change from

=IF(B7>0,VLOOKUP(B7,'\\Inet\Data\peg-users\Pegasus\17 Databases\LABOR REPORT 20080710xls'!EmpList,2,FALSE),"-")

to

=IF(B7>0,VLOOKUP(B7,'\\Inet\Data\peg-users\Pegasus\17 Databases\LABOR REPORT 20080711xls'!EmpList,2,FALSE),"-")

so that the reference now refers to the new sheet title.

Is there any way to do this?
 

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.
It's difficult to tell from your formula but this looks like a different sheet in a different workbook -- is that true? Your post only mentions sheet names/titles as the difference. How does this work?

I'm confused why your files are named like Filexls instead of File.xls

AB
 
Upvote 0
Whoops. Yes the formula should read:

=IF(B7>0,VLOOKUP(B7,'\\Inet\Data\peg-users\Peg\17 Databases\LABOR REPORT 20080710.xls'!EmpList,2,FALSE),"-")

The VLOOKUP formula references a separate sheet in another folder on our network drive.
 
Upvote 0
I don't do this much myself, but I think Indirect() is the way to go - it would only return a value if the other sheet is open...

Possibly, using Barry's suggestion might help, but we'd need to build the string for the address...There's an add-in called MoreFunc that is supposed to help with this (using Indirect with external references) ... I'm not getting to the download page anymore these days unfortunately...
 
Upvote 0
Okay, this seems to work as far as it goes, but the other workbook must be open...

In Cell A1:
=INDIRECT("'"&C1&C2&"'!"&"$A$1")

In Cell C1:
C:\TEMP\[TestBook.xls]Labor Report
(There's a space at the end)

In Cell C2:
20080710


Might be a tad easier with no spaces in the sheet names...
 
Upvote 0
No luck here with any Index() variations... :(
 
Upvote 0
That will not work for my purpose. I don't want to open the sheets I am referencing. My VLOOKUP formula finds the "emplist" in an unopened sheet. I would like this VLOOKUP to work the same, but I would like to avoid having to write a long formula. Most of the formula stays the same, it is just the excel file name that changes. I may try just writing a macro that will copy and paste the cell info into the formula. I just wish excel had a way to do it the way I want.

Okay, this seems to work as far as it goes, but the other workbook must be open...

In Cell A1:
=INDIRECT("'"&C1&C2&"'!"&"$A$1")

In Cell C1:
C:\TEMP\[TestBook.xls]Labor Report
(There's a space at the end)

In Cell C2:
20080710


Might be a tad easier with no spaces in the sheet names...
 
Upvote 0
Upvote 0

Forum statistics

Threads
1,213,510
Messages
6,114,048
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