Vlookup with variable sheet name

I have an excel workbook where employees enter their weekly work orders. I also have a control workbook where the actual time worked on each job is stored. I am using the vlookup function to pull the actual labor hours from the control workbook to the employee's schedule. The problem is that every week a new set of labor hours is created. I have been using the vlookup function to pull the labor hours however I have to replace the labor hours sheet each week with new data. I would like the vlookup to be able to reference a sheet name that matches the specified date for the schedule. I tried using the indirect function for this but the control workbook has to be open and that is not an option. Is there a way to do this?
Who is Participating?
ScriptAddictConnect With a Mentor Commented:
If you are going to be doing this kind of work a lot I highly recommend learning VBA.  It is essentially a programming language and as such can do pretty much anything.  It is designed to be an easy to learn language.

The original solution I proposed would still work.  Change your process to this.  

Save current week in archive folder.  

Open vlookup file.  

Delete all content on the tab (but not the tab since that will messup your vlookup formula)

Select entire current week sheet and Copy to clipboard.

Paste current week into tab on vlookup workbook.  


This would:
Archive each week, (Before putting it into the vlookup workbook)
Not require vlookup updates
Not require any coding
Require manual work each week to do it.

Alternatively you could set the entire thing up in VBA
This would:
Potentially do everything each week.  
Require coding.

There are a couple of different ways to do this.  

First you could not delete the old sheet, and add in a new sheet, but instead delete all the content on the sheet and paste the new content on it.  This would solve your problem assuming the format between the two sheets are identical.

Alternatively you could use TODAY() as part of your vlookup formula to make sure you are always pulling the correct date tab

Third you could use VBA.
RMcAhonAuthor Commented:
I want to be able to keep the old data available because there are times when an employee may be entering historical data. Currently I am replacing the labor sheet each week with the new data and dealing with historical information manually. However this is not a long term solution. I am not very familiar with VBA. Is there a way to get the vlookup to go to the sheet with a name specified in a specific cell?
Hi RMcAhon.

Not for points...

Is there a way to get the vlookup to go to the sheet with a name specified in a specific cell?
You don't need VBA for this - see the INDIRECT function.

Edit: Apologies, I just noticed that you mentioned this in the question. I'll post an example for you.

Edit2: Please see attached. The formula is ...

RMcAhonAuthor Commented:
I think I am going to have to go the VBA route. While I have used VBA some this is much more complicated than what I have done in the past. Do you know of any good resources or any advice to get me started.

Thanks for the help.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.