Hi all. Please see attached sheet. I have a VLookup which will add start/finish and meal relief times. I am trying to figure out how to leave a cell blank for a day when the times are not required. In the sheet I only want the times to show on Mon, Wed, Thu and Fri. For Tue, Sat and Sun I want the times blank.
What I need os for the VLookup to return a blank cell when the weekday cell value is '*'
What am I missing?
Thanks in advance
you could add this to the vlookup... in D7 =IF(OR(D4="Tuesday","Saturday","Sunday"),"",IFERROR(VLOOKUP(A7,'Duty Times'!$A$1:$B$530,2,0),""))
this in H7... =IF(OR(H4="Tuesday","Saturday","Sunday"),"",IFERROR(VLOOKUP(E7,'Duty Times'!$A$1:$B$530,2,0),""))
You'll have to adjust it for each of the cells in row 7 since your lookups vary in each cell.
but I would do my best to get rid of the merged cells in row 4 as they will mess up the formula if you are trying to drag it toward the right. (maybe one row hidden with the day of the week above D4 and E4 and F4 etc where it says Monday, then same for H4 etc for Tuesday.
Make contributors happy, click on the "* Add Reputation" as a way to say thank you.
Sam Capricci
In D7 using your formula as a base, for example,
Formula:
Please Login or Register to view this content.
I would switch from VLOOKUP to INDEX/MATCH and modify your table on Duty Times so that the headers match what's in Row 4 of MASTER. Then you can just copy D7 across and down rather than reinventing the wheel.
Then the base formula would be
Formula:Please Login or Register to view this content.
and to make it skip Tues,Sat and Sun
See Row 8 of attached workbook.Formula:Please Login or Register to view this content.
ChemistB
My 2?
substitute commas with semi-colons if your region settings requires
Don't forget to mark threads as "Solved" (Edit First post>Advanced>Change Prefix)
If I helped, Don't forget to add to my reputation (click on the little star at bottom of this post)
Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble
Many thanks. The shaded cells all have a value of * in them so I have changed it to the following based on your help
=IF(OR(AF7="*"),"",IFERROR(VLOOKUP(A7,'Duty Times'!$A$1:$B$530,2,0),""))
I'd just like to say thank you very much.
Thank you. I didn't see your reply until I had finished modifying Sambo Kid's formula as above. I appreciate your help.Originally Posted by ChemistB
In D7 using your formula as a base, for example,
Formula:Please Login or Register to view this content.
I would switch from VLOOKUP to INDEX/MATCH and modify your table on Duty Times so that the headers match what's in Row 4 of MASTER. Then you can just copy D7 across and down rather than reinventing the wheel.
Then the base formula would be
Formula:Please Login or Register to view this content.
and to make it skip Tues,Sat and Sun
See Row 8 of attached workbook.Formula:Please Login or Register to view this content.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks