Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Vlookup with variable sheet name

Posted on 2013-01-28
7
Medium Priority
?
386 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
5 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 2000 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

Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

Question has a verified solution.

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

With its various features, Office 365 can not only help you with your day-to-day business tasks, it can also do wonders for your marketing campaign.
MS Outlook undoubtedly is the most widely used email client.Its user-friendliness, cost effectiveness, and availability with Microsoft Office Suite make it the most popular email application.  Its compatibility with Microsoft applications like Exch…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…

581 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