Solved

Vlookup with variable sheet name

Posted on 2013-01-28
7
362 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

Office 365 Training for Admins - 7 Day Trial

Learn how to provision tenants, synchronize on-premise Active Directory, implement Single Sign-On, customize Office deployment, and protect your organization with eDiscovery and DLP policies.  Only from Platform Scholar.

Question has a verified solution.

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

Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
I was prompted to write this article after the recent World-Wide Ransomware outbreak. For years now, System Administrators around the world have used the excuse of "Waiting a Bit" before applying Security Patch Updates. This type of reasoning to me …
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

622 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