Nested IF statements are returning FALSE values - cannot find error in code!
Hi guys,
I'm using nested IF statements to return the department and time various employees are working at a bakery.
My formula to return the time they're working returns a FALSE rather than the shift time and I'm having a hard time working out why - help would be greatly appreciated!
Cheers,
Alex
Fairbank roster summary page.xlsx
Hi and welcome to the forum
your 2nd and 3rd IF's dont have the 3rd argument, so take a look at that.
However, see if this shorter formula will do what you want? ...
=INDEX(INDIRECT(H3&"!$E:$E"),MATCH(Summary!B3,INDIRECT(H3&"!$D:$D"),0))
1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
2. If your question is resolved, mark it SOLVED using the thread tools
3. Click on the star if you think someone helped you
Regards
Ford
same approach with the above.. just notice the difference between the ranges used so posted this one.
Formula:
Please Login or Register to view this content.
I think people forget the word "THANK YOU!!!!" Do you still know it???
There is a little star ( ADD REPUTATION ) below those person who helped you. Click it to say your "PRIVATE APPRECIATION TO THEIR EFFORT ON THEIR CONTRIBUTIONS "
Regards,
Vladimir
That short formula is golden and does exactly what I need it to. Thank you so much!
May have celebrated too early? Times are not being returned for 2 out of 5 departments.
Fairbank roster summary page.xlsx
look at post #3
you have different ranges for the department.
Load Bay and Stores have the same ranges D:D and C:C respectively
the remaining department uses E:E and D:D respectively
Thanks Vlad, there was no daya in the other sheets, so I could not test them. And if that works, maybe this slightly shorter 1 will work too?...
=INDEX(INDIRECT(H3&IF(OR(H3="Load Bay",H3="Stores"),"!$D:$D","!$E:$E")),MATCH(Summary!B3,INDIRECT(H3&"!$C:$C"),0))
Last edited by FDibbins; 11-03-2013 at 10:19 PM.
Added an extra column to Load Bay and Stores so that all departments have the same range.
=IFERROR(INDEX(INDIRECT(AX3&"!$W:$W"),MATCH(Summary!B3,INDIRECT(AX3&"!$V:$V"),0)),"")
IFERROR to get rid of all the #REFs
Changed the name of the Load Bay and Food Coatings tabs - the spaces were causing problems.
Works like a charm. Thanks guys.
you'r welcome. If this answered your question, please take a moment to mark the thread as "solved" - it helps keep things neat and tidy lol, and consider adding the reputation to those that helped (see points 2 & 3 below my 1st post to you)
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks