I have a simple workbook consisting of a sheet for vacation date entry and a second sheet which will provide a visual representation of the vacation period booked.
How do I take the dates on one sheet and create individual cell formatting ie shaded red (or some other entry ie "V") on the second sheet for the date ranges specified?
Thanks for any suggestions.
Try something like this, starting in Cell C4 and fill to the right
it is an array formula, so you must confirm with Ctrl+Shift+Enter
=IF((Andrew!$B16:$C29<=C2)*(Andrew!$D16:$E29>=C2),"V","")
If you liked my solution, please click on the Star -- to add to my reputation
If your issue as been resolved, please clearly state so and mark the thread as [SOLVED] using the thread tools just above the first post.
Thank you very much for the quick response. This does exactly what I want with one exception, it stops after the first vacation period. Upon more testing if the first row is empty in the "Booked Days Tracking" section it stops and does not pick up the first completed vacation period row at all.
Is there a way to repeat the formula for all rows in the "Booked Days Tracking" section?
Last edited by paulrh; 03-13-2012 at 03:50 AM.
Inelegant solution found without resorting to VBA:
=IF((David!$B$16:$B$29<=C2)*(David!$D$16:$D$29>=C2),"V",IF((David!$B$17:$B$29<=C2)*(David!$D$17:$D$29>=C2),"V",IF((David!$B$18:$B$29<=C2)*(David!$D$18:$D$29>=C2),"V",IF((David!$B$19:$B$29<=C2)*(David!$D$19:$D$29>=C2),"V",IF((David!$B$20:$B$29<=C2)*(David!$D$20:$D$29>=C2),"V",IF((David!$B$21:$B$29<=C2)*(David!$D$21:$D$29>=C2),"V",IF((David!$B$22:$B$29<=C2)*(David!$D$22:$D$29>=C2),"V",IF((David!$B$23:$B$29<=C2)*(David!$D$23:$D$29>=C2),"V",IF((David!$B$24:$B$29<=C2)*(David!$D$24:$D$29>=C2),"V",IF((David!$B$25:$B$29<=C2)*(David!$D$25:$D$29>=C2),"V",IF((David!$B$26:$B$29<=C2)*(David!$D$26:$D$29>=C2),"V",IF((David!$B$27:$B$29<=C2)*(David!$D$27:$D$29>=C2),"V",IF((David!$B$28:$B$29<=C2)*(David!$D$28:$D$29>=C2),"V",IF((David!$B$29:$B$29<=C2)*(David!$D$29:$D$29>=C2),"V",""))))))))))))))
try this solution, again confirmed with Ctrl+Shift+Enter
=IF(SUM(IF((Andrew!$B$16:$B$29<=C$2)*(Andrew!$D$16:$D$29>=C$2),1,0))=1,"V","")
This works perfectly. Thank you so much.
I clicked your Star but received no feedback that it did anything. Other forms I am on have a "thanks" or similar indicator.
Last edited by paulrh; 03-13-2012 at 01:06 PM.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks