Solved

Vlookup with variable sheet name

Posted on 2013-01-28
7
328 Views
Last Modified: 2013-09-25
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?
0
Comment
Question by:RMcAhon
  • 2
  • 2
7 Comments
 
LVL 11

Expert Comment

by:ScriptAddict
ID: 38828081
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.
0
 

Author Comment

by:RMcAhon
ID: 38828165
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?
0
 
LVL 11

Accepted Solution

by:
ScriptAddict earned 500 total points
ID: 38828242
Yes,
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.  

Done.

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.




-SA
0
 
LVL 26

Expert Comment

by:redmondb
ID: 38828333
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 ...
=VLOOKUP(A2,INDIRECT("'"&$C$1&"'!$A$2:$B$9"),2,0)

Regards,
Brian.Indirect-Vlookup-Example.xls
0
 

Author Comment

by:RMcAhon
ID: 38832076
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.
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

PaperPort has a feature called the "Send To Bar". It provides a convenient, drag-and-drop interface for using other installed software, such as Microsoft Office. However, this article shows that the latest Office 2016 apps (installed with an Office …
No matter the version of Windows you are using, you may have some problems with Windows Search running too slow or possibly not running at all. Before jumping into how you can solve this issue, just know there are many other viable alternative deskt…
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

932 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now