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?
 

Some videos you may like

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).

xenou

MrExcel MVP
Joined
Mar 2, 2007
Messages
16,793
Office Version
  1. 2019
Platform
  1. Windows
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
 

keithcomic

New Member
Joined
Jul 10, 2008
Messages
4
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.
 

xenou

MrExcel MVP
Joined
Mar 2, 2007
Messages
16,793
Office Version
  1. 2019
Platform
  1. Windows
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...
 

xenou

MrExcel MVP
Joined
Mar 2, 2007
Messages
16,793
Office Version
  1. 2019
Platform
  1. Windows
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...
 

xenou

MrExcel MVP
Joined
Mar 2, 2007
Messages
16,793
Office Version
  1. 2019
Platform
  1. Windows

ADVERTISEMENT

No luck here with any Index() variations... :(
 

keithcomic

New Member
Joined
Jul 10, 2008
Messages
4
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...
 

xenou

MrExcel MVP
Joined
Mar 2, 2007
Messages
16,793
Office Version
  1. 2019
Platform
  1. Windows

Watch MrExcel Video

Forum statistics

Threads
1,122,511
Messages
5,596,581
Members
414,079
Latest member
Frills

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
Top