Vlookups, IF's..and Oh No!

Vlookups, IF's..and Oh No!

Post by Shade » Wed, 30 Nov 2005 12:42:56



There are three sheets. Customer Service Roster, Customer Servic
Timesheet and Master Data.


Sheet *"Customer Service Roster" *
It is split into five sections, one for each day (Mon-Fri). You clic
on a cell under the date and it brings up a list to select. Once yo
select the option, the start time appears on the left of it. Prett
simple.

A5=IF(ISNA((VLOOKUP(B5,'Maste
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 th
dates, with start time, finish time and total hours worked. Once yo
select the employee on Customer Service Roster, it should update th
time started on the Timesheet as well.

C7=IF(ISNA((VLOOKUP(A7,'Customer Servic
Roster'!A45:B56,1,0))),0,(VLOOKUP(A7,'Customer Servic
Roster'!A45:B56,1,0)))



BUT!

It's doing it's job on the Roster, however the data is not transferrin
to the Timesheet. I have attached it for your reviews, and assistance.
There are multiple areas that have cond format on them, as well a
hidden, however you should be able to see what I'm trying t
accomplish. Any help is appreciated, and is urgently needed!

XXXX@XXXXX.COM

+-------------------------------------------------------------------
|Filename: ROSTER SHEET.zip
|Download: http://www.yqcomputer.com/
+-------------------------------------------------------------------
 
 
 

Vlookups, IF's..and Oh No!

Post by cvolker » Wed, 30 Nov 2005 13:23:01


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.yqcomputer.com/
View this thread: http://www.yqcomputer.com/

 
 
 

Vlookups, IF's..and Oh No!

Post by Shade » Wed, 30 Nov 2005 14:15:33


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.yqcomputer.com/
View this thread: http://www.yqcomputer.com/