Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Vlookups, IF's..and Oh No!
There are three sheets. Customer Service Roster, Customer Service Timesheet and Master Data. Sheet *"Customer Service Roster" * It is split into five sections, one for each day (Mon-Fri). You click on a cell under the date and it brings up a list to select. Once you select the option, the start time appears on the left of it. Pretty simple. A5=IF(ISNA((VLOOKUP(B5,'Master Data'!J79:L106,3,0))),0,(VLOOKUP(B5,'Master Data'!J79:L106,3,0))) B5=Validation List Sheet *"Customer Service Timesheet"* This has the employees listed on A6:A20. Then it has to the right the dates, with start time, finish time and total hours worked. Once you select the employee on Customer Service Roster, it should update the time started on the Timesheet as well. C7=IF(ISNA((VLOOKUP(A7,'Customer Service Roster'!A45:B56,1,0))),0,(VLOOKUP(A7,'Customer Service Roster'!A45:B56,1,0))) BUT! It's doing it's job on the Roster, however the data is not transferring to the Timesheet. I have attached it for your reviews, and assistance. There are multiple areas that have cond format on them, as well as hidden, however you should be able to see what I'm trying to accomplish. Any help is appreciated, and is urgently needed! +-------------------------------------------------------------------+ |Filename: ROSTER SHEET.zip | |Download: http://www.excelforum.com/attachment.php?postid=4079 | +-------------------------------------------------------------------+ -- Shadez ------------------------------------------------------------------------ Shadez's Profile: http://www.excelforum.com/member.php...fo&userid=3481 View this thread: http://www.excelforum.com/showthread...hreadid=488959 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Vlookups, IF's..and Oh No!
It looks like the vlookups on your Customer Service Timesheet are set up incorrectly. You have them looking for the person's name in column A, C, E, etc on the Customer Service Roster tab. The problem is that the names are in columns B, D, F, etc (i.e. the right hand column rather than the lefthand column). To get it to work correctly, you can either switch the columns on the Customer Service Roster tab (since the looked up value always has the be in the left most column) or you can use and Index/Match combo function and leave the columns as-is. If others are going to work with this spreadsheet, the vlookup is probably the way to go since most people are familiar with this function - and Index/Match may confuse them otherwise. Let me know if you need more help, Chad -- cvolkert ------------------------------------------------------------------------ cvolkert's Profile: http://www.excelforum.com/member.php...o&userid=24380 View this thread: http://www.excelforum.com/showthread...hreadid=488959 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Vlookups, IF's..and Oh No!
You are a champ! This is why one should never /ever/ try and do excel formulas from 10:00 PM to 3:00AM! Was getting so frustrated! How our problems are always something small that we overlook, yet how those problems compound themselves in the end! Thanks again! -- Shadez ------------------------------------------------------------------------ Shadez's Profile: http://www.excelforum.com/member.php...fo&userid=3481 View this thread: http://www.excelforum.com/showthread...hreadid=488959 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|