Solved

Vlookup with variable sheet name

Posted on 2013-01-28
7
354 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Suggested Solutions

Microsoft Office Picture Manager was included in Office 2003, 2007, and 2010, but not in Office 2013. Users had hopes that it would be in Office 2016/Office 365, but it is not. Fortunately, the same zero-cost technique that works to install it with …
How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
Learn how to make your own table of contents in Microsoft Word using paragraph styles and the automatic table of contents tool. We'll be using the paragraph styles in Word’s Home toolbar to help you create a table of contents. Type out your initial …
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.

752 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