Need help with IF statements

[Dave Potter]

I'm really struggling with getting this formula to do what I want. Any
help would again be appreciated. Some have helped get me to where I
am. I just keep running into more conditions I have to meet.
Here is the formula (which works fine except for one condition) and
then I'll explain what else I need it to do.

=IF((VLOOKUP($A8,TUE!$A$11:$CB$29,1,FALSE)=0),"",IF((VLOOKUP($A8,TUE!$A$11:$CB$29,2,FALSE)=0),VLOOKUP($A8,TUE!$A$11:$CB$29,79,FALSE),VLOOKUP($A8,TUE!$A$11:$CB$29,2,FALSE)))

Sheet tabs are Mon, Tue, Wed, Thur, Fri, Sat, Sun.

The VLOOKUPs are looking for matching peoples names.
If I enter David in MON!$A11 and enter the Time in and Time out in B11
and C11, Then I enter David in CREW SCHEDULE!A8 , the values in
MON!B12 and MON!C11 should be returned in CREW SCHEDULE!B8 AND C8
The formula above is in CREW SCHEDULE!K8 AND L8.


A8 B8 C8 K8 L8
DAVID 7:00 AM 4:00 PM #N/A #N/A


Since A8 is no longer blank (David works on Monday) and there is no
matching name in TUE!$A$11:$CB$29 (David doesnt work on Tuesday), Im
getting the #N/A. If David worked Tuesday, the formula would work
fine. I need to get rid of the #N/A though because I need to sum up
the column. This is very complicated since there are so many
variables. The =0 part needs to stay there.
Thats the best I can explain it right now. If any one cares to tackle
this, I will be watching the group so I can provide further
clarifications if needed.

Thank you
David

 

[Dave Peterson]

I don't think that first =0 is correct.

=IF((VLOOKUP($A8,TUE!$A$11:$CB$29,1,FALSE)=0),.....

If you put David (a very nice name, by the way) in A11, then the only way you'll
return 0 is when $a8 contains 0.

maybe something like:

=IF(ISERROR(VLOOKUP($A8,TUE!$A$11:$CB$29,1,FALSE)),0,
IF((VLOOKUP($A8,TUE!$A$11:$CB$29,2,FALSE)=0),
VLOOKUP($A8,TUE!$A$11:$CB$29,79,FALSE),
VLOOKUP($A8,TUE!$A$11:$CB$29,2,FALSE)))

 

[Dave Potter]

I don't think that first =0 is correct.

=IF((VLOOKUP($A8,TUE!$A$11:$CB$29,1,FALSE)=0),.....

Dave,

The way the Mon-Fri sheets are set up requires that A11: =CK11.
Its quite difficult to explain without seeing it. This is a Bar Graph
Scheduling program. In Column A is Day shift names and in Column CK is
Night shift. But since the VLOOKUP looks for the name in Column 1,
I've had to make Column A equal column CK just to get all the names
over there. The names for night shift have to be in Column CK because
of separating Day shift hours from night shift hours.
Originally, I had the formula as follows

=IF(ISNA(VLOOKUP($A8,TUE!$A$11:$CB$29,1,FALSE),"",.....

however, other variables came in to play which required me to change
it. I dont know if that explains it any better, hope so.
Thanks
David

 

[Dave Potter]

I think the problem is that the first section of the formula is
returning a #NA because David is entered in the Crew Schedule!A8 since
he works Monday, but when David doesnt work on a different day his
name isnt found and therefore returns the #NA. Is there a way to test
two things in the first part? I need to test for #NA and =0 if
possible?
thanks,
David

 

[Dave Potter]

I think the problem is that the first section of the formula is
returning a #NA because David is entered in the Crew Schedule!A8 since
he works Monday, but when David doesnt work on a different day his
name isnt found and therefore returns the #NA. Is there a way to test
two things in the first part? I need to test for #NA and =0 if
possible?
thanks,
David

 

[Dave Potter]

Nevermind, I think I've worked it out by taking out the =0 condition
and putting in an ISNA condition which returns 0 (translates to 12:00
AM) and then conditional formatting so that the 12:00 AM doesnt show
up (format text to white) and since the in is 12:00 and the out is
12:00 it doesnt affect my totals at the bottom.
Thanks to Dave Peterson who orignally pointed out the problem with the
=0 thing.
Thanks,
David