Hello, I'm new here and I've been scouring this forum and the web for a solution to my scheduling conundrum....I have workshop and I want to be able to know what areas will be busy in the coming weeks/months, based on the current demand. So, I have a table where I input new jobs and I want this data to appear on a weekly/daily schedule. Sounds simple but I'm going round in circles...
The Input table looks like this:
[TABLE="width: 559"]
<colgroup><col width="78" style="width: 78pt;"><col width="65" span="2" style="width: 65pt;"><col width="91" style="width: 91pt;"><col width="65" span="4" style="width: 65pt;"></colgroup><tbody>[TR]
[TD="class: xl66, width: 78, bgcolor: #C4BD97"]PROJECT[/TD]
[TD="class: xl66, width: 65, bgcolor: #C4BD97"]CLIENT[/TD]
[TD="class: xl66, width: 65, bgcolor: #C4BD97"]STATUS[/TD]
[TD="class: xl66, width: 91, bgcolor: #C4BD97"]PROJECT MANAGER[/TD]
[TD="class: xl67, width: 65, bgcolor: #4F81BD"]START[/TD]
[TD="class: xl67, width: 65, bgcolor: #4F81BD"]END[/TD]
[TD="class: xl68, width: 65, bgcolor: #4F81BD"]DAYS[/TD]
[TD="class: xl63, width: 65, bgcolor: #4F81BD"]BAY REF[/TD]
[/TR]
[TR]
[TD="class: xl69, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DCE6F1]#DCE6F1[/URL] "]Tree House[/TD]
[TD="class: xl69, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DCE6F1]#DCE6F1[/URL] "] [/TD]
[TD="class: xl69, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DCE6F1]#DCE6F1[/URL] "]CONFIRMED[/TD]
[TD="class: xl69, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DCE6F1]#DCE6F1[/URL] "]Charlie[/TD]
[TD="class: xl70, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DCE6F1]#DCE6F1[/URL] , align: right"]07/06/18[/TD]
[TD="class: xl70, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DCE6F1]#DCE6F1[/URL] , align: right"]23/07/18[/TD]
[TD="class: xl64, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DCE6F1]#DCE6F1[/URL] , align: right"]33[/TD]
[TD="class: xl65, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DCE6F1]#DCE6F1[/URL] "]W1[/TD]
[/TR]
[TR]
[TD="class: xl71"]Decking[/TD]
[TD="class: xl71"] [/TD]
[TD="class: xl71"]CONFIRMED[/TD]
[TD="class: xl69, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DCE6F1]#DCE6F1[/URL] "]Charlie[/TD]
[TD="class: xl72, align: right"]12/06/18[/TD]
[TD="class: xl72, align: right"]17/06/18[/TD]
[TD="class: xl73, align: right"]4[/TD]
[TD="class: xl65, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DCE6F1]#DCE6F1[/URL] "]W2[/TD]
[/TR]
[TR]
[TD="class: xl69, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DCE6F1]#DCE6F1[/URL] "]Interior Doors[/TD]
[TD="class: xl69, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DCE6F1]#DCE6F1[/URL] "] [/TD]
[TD="class: xl69, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DCE6F1]#DCE6F1[/URL] "]ESTIMATE[/TD]
[TD="class: xl69, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DCE6F1]#DCE6F1[/URL] "]Charlie[/TD]
[TD="class: xl70, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DCE6F1]#DCE6F1[/URL] , align: right"]12/06/18[/TD]
[TD="class: xl70, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DCE6F1]#DCE6F1[/URL] , align: right"]26/06/18[/TD]
[TD="class: xl64, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DCE6F1]#DCE6F1[/URL] , align: right"]11[/TD]
[TD="class: xl65, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DCE6F1]#DCE6F1[/URL] "]W4[/TD]
[/TR]
[TR]
[TD="class: xl71"]Planters[/TD]
[TD="class: xl71"] [/TD]
[TD="class: xl71"]ESIMATE[/TD]
[TD="class: xl71"]Gary[/TD]
[TD="class: xl72, align: right"]07/05/18[/TD]
[TD="class: xl72, align: right"]30/07/18[/TD]
[TD="class: xl73, align: right"]61[/TD]
[TD="class: xl65, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DCE6F1]#DCE6F1[/URL] "]W7,W8[/TD]
[/TR]
[TR]
[TD="class: xl69, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DCE6F1]#DCE6F1[/URL] "]Window Frames[/TD]
[TD="class: xl69, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DCE6F1]#DCE6F1[/URL] "] [/TD]
[TD="class: xl69, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DCE6F1]#DCE6F1[/URL] "]CONFIRMED[/TD]
[TD="class: xl69, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DCE6F1]#DCE6F1[/URL] "]Gary[/TD]
[TD="class: xl70, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DCE6F1]#DCE6F1[/URL] , align: right"]07/05/18[/TD]
[TD="class: xl70, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DCE6F1]#DCE6F1[/URL] , align: right"]16/07/18[/TD]
[TD="class: xl64, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DCE6F1]#DCE6F1[/URL] , align: right"]51[/TD]
[TD="class: xl65, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DCE6F1]#DCE6F1[/URL] "]W2[/TD]
[/TR]
[TR]
[TD="class: xl71"]External Doors[/TD]
[TD="class: xl71"] [/TD]
[TD="class: xl71"]CONFIRMED[/TD]
[TD="class: xl71"]Jane[/TD]
[TD="class: xl72, align: right"]07/05/18[/TD]
[TD="class: xl72, align: right"]16/07/18[/TD]
[TD="class: xl73, align: right"]51[/TD]
[TD="class: xl65, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DCE6F1]#DCE6F1[/URL] "]W3[/TD]
[/TR]
[TR]
[TD="class: xl69, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DCE6F1]#DCE6F1[/URL] "]Hoarding[/TD]
[TD="class: xl69, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DCE6F1]#DCE6F1[/URL] "] [/TD]
[TD="class: xl69, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DCE6F1]#DCE6F1[/URL] "]CONFIRMED[/TD]
[TD="class: xl71"]Jane[/TD]
[TD="class: xl70, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DCE6F1]#DCE6F1[/URL] , align: right"]11/06/18[/TD]
[TD="class: xl70, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DCE6F1]#DCE6F1[/URL] , align: right"]30/07/18[/TD]
[TD="class: xl64, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DCE6F1]#DCE6F1[/URL] , align: right"]36[/TD]
[TD="class: xl65, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DCE6F1]#DCE6F1[/URL] "]W8[/TD]
[/TR]
[TR]
[TD="class: xl71"]Shelving Unit[/TD]
[TD="class: xl71"] [/TD]
[TD="class: xl71"]ESTIMATE[/TD]
[TD="class: xl71"]Jane[/TD]
[TD="class: xl72, align: right"]11/06/18[/TD]
[TD="class: xl72, align: right"]30/07/18[/TD]
[TD="class: xl73, align: right"]36[/TD]
[TD="class: xl65, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DCE6F1]#DCE6F1[/URL] "]W8[/TD]
[/TR]
[TR]
[TD="class: xl69, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DCE6F1]#DCE6F1[/URL] "]Timber Frame[/TD]
[TD="class: xl69, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DCE6F1]#DCE6F1[/URL] "] [/TD]
[TD="class: xl69, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DCE6F1]#DCE6F1[/URL] "]ESTIMATE[/TD]
[TD="class: xl71"]Philip[/TD]
[TD="class: xl70, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DCE6F1]#DCE6F1[/URL] , align: right"]23/07/18[/TD]
[TD="class: xl70, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DCE6F1]#DCE6F1[/URL] , align: right"]10/09/18[/TD]
[TD="class: xl64, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DCE6F1]#DCE6F1[/URL] , align: right"]36[/TD]
[TD="class: xl65, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DCE6F1]#DCE6F1[/URL] "]W08,W09,W10[/TD]
[/TR]
[TR]
[TD="class: xl71"]Dining Table[/TD]
[TD="class: xl71"] [/TD]
[TD="class: xl71"]ESTIMATE[/TD]
[TD="class: xl71"]Philip[/TD]
[TD="class: xl72, align: right"]23/07/18[/TD]
[TD="class: xl72, align: right"]10/09/18[/TD]
[TD="class: xl73, align: right"]36[/TD]
[TD="class: xl65, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DCE6F1]#DCE6F1[/URL] "]W9[/TD]
[/TR]
[TR]
[TD="class: xl69, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DCE6F1]#DCE6F1[/URL] "]Garden Furniture[/TD]
[TD="class: xl69, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DCE6F1]#DCE6F1[/URL] "] [/TD]
[TD="class: xl69, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DCE6F1]#DCE6F1[/URL] "]ESTIMATE[/TD]
[TD="class: xl71"]Philip[/TD]
[TD="class: xl70, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DCE6F1]#DCE6F1[/URL] , align: right"]25/06/18[/TD]
[TD="class: xl70, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DCE6F1]#DCE6F1[/URL] , align: right"]23/07/18[/TD]
[TD="class: xl64, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DCE6F1]#DCE6F1[/URL] , align: right"]21[/TD]
[TD="class: xl65, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DCE6F1]#DCE6F1[/URL] "]W5[/TD]
[/TR]
</tbody>[/TABLE]
The important data in columns "start date", "finish date" and "bay ref" as I want the information to appear in another sheet, that looks like this:
[TABLE="width: 1079"]
<colgroup><col width="78" style="width: 78pt;"><col width="65" span="2" style="width: 65pt;"><col width="91" style="width: 91pt;"><col width="65" span="12" style="width: 65pt;"></colgroup><tbody>[TR]
[TD="class: xl63, width: 78"] [/TD]
[TD="class: xl64, width: 65, align: right"]07/05/18[/TD]
[TD="class: xl64, width: 65, align: right"]08/05/18[/TD]
[TD="class: xl64, width: 91, align: right"]09/05/18[/TD]
[TD="class: xl64, width: 65, align: right"]10/05/18[/TD]
[TD="class: xl64, width: 65, align: right"]11/05/18[/TD]
[TD="class: xl64, width: 65, align: right"]12/05/18[/TD]
[TD="class: xl64, width: 65, align: right"]13/05/18[/TD]
[TD="class: xl64, width: 65, align: right"]14/05/18[/TD]
[TD="class: xl64, width: 65, align: right"]15/05/18[/TD]
[TD="class: xl64, width: 65, align: right"]16/05/18[/TD]
[TD="class: xl64, width: 65, align: right"]17/05/18[/TD]
[TD="class: xl64, width: 65, align: right"]18/05/18[/TD]
[TD="class: xl64, width: 65, align: right"]19/05/18[/TD]
[TD="class: xl64, width: 65, align: right"]20/05/18[/TD]
[TD="class: xl64, width: 65, align: right"]21/05/18[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DCE6F1]#DCE6F1[/URL] "]BAY W1[/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DCE6F1]#DCE6F1[/URL] "]BAY W2[/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DCE6F1]#DCE6F1[/URL] "]BAY W3[/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DCE6F1]#DCE6F1[/URL] "]BAY W4[/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DCE6F1]#DCE6F1[/URL] "]BAY W5[/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DCE6F1]#DCE6F1[/URL] "]BAY W6[/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DCE6F1]#DCE6F1[/URL] "]BAY W7[/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DCE6F1]#DCE6F1[/URL] "]BAY W8[/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DCE6F1]#DCE6F1[/URL] "]BAY W9[/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DCE6F1]#DCE6F1[/URL] "]BAY W10[/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[/TR]
</tbody>[/TABLE]
I would like a conditional formatting equation which would highlight a bay, when it is scheduled to be used that week so at a glance, I know what areas are busy and when. The added issue is that there might be multiple bays appearing in the cell in sheet 1, which should be separated and shown as in use according to the start/end date.
I think I need something that says: if the row header "BAY W*" appears in Bay Ref column of table 1, and the column header "date" is greater than "start date" and less than "end date" in sheet 1 then highlight the cell. But, Excel2010 doesn't understand my primitive language!...If anybody can advise, it would be much appreciated
Thanks
The Input table looks like this:
[TABLE="width: 559"]
<colgroup><col width="78" style="width: 78pt;"><col width="65" span="2" style="width: 65pt;"><col width="91" style="width: 91pt;"><col width="65" span="4" style="width: 65pt;"></colgroup><tbody>[TR]
[TD="class: xl66, width: 78, bgcolor: #C4BD97"]PROJECT[/TD]
[TD="class: xl66, width: 65, bgcolor: #C4BD97"]CLIENT[/TD]
[TD="class: xl66, width: 65, bgcolor: #C4BD97"]STATUS[/TD]
[TD="class: xl66, width: 91, bgcolor: #C4BD97"]PROJECT MANAGER[/TD]
[TD="class: xl67, width: 65, bgcolor: #4F81BD"]START[/TD]
[TD="class: xl67, width: 65, bgcolor: #4F81BD"]END[/TD]
[TD="class: xl68, width: 65, bgcolor: #4F81BD"]DAYS[/TD]
[TD="class: xl63, width: 65, bgcolor: #4F81BD"]BAY REF[/TD]
[/TR]
[TR]
[TD="class: xl69, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DCE6F1]#DCE6F1[/URL] "]Tree House[/TD]
[TD="class: xl69, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DCE6F1]#DCE6F1[/URL] "] [/TD]
[TD="class: xl69, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DCE6F1]#DCE6F1[/URL] "]CONFIRMED[/TD]
[TD="class: xl69, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DCE6F1]#DCE6F1[/URL] "]Charlie[/TD]
[TD="class: xl70, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DCE6F1]#DCE6F1[/URL] , align: right"]07/06/18[/TD]
[TD="class: xl70, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DCE6F1]#DCE6F1[/URL] , align: right"]23/07/18[/TD]
[TD="class: xl64, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DCE6F1]#DCE6F1[/URL] , align: right"]33[/TD]
[TD="class: xl65, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DCE6F1]#DCE6F1[/URL] "]W1[/TD]
[/TR]
[TR]
[TD="class: xl71"]Decking[/TD]
[TD="class: xl71"] [/TD]
[TD="class: xl71"]CONFIRMED[/TD]
[TD="class: xl69, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DCE6F1]#DCE6F1[/URL] "]Charlie[/TD]
[TD="class: xl72, align: right"]12/06/18[/TD]
[TD="class: xl72, align: right"]17/06/18[/TD]
[TD="class: xl73, align: right"]4[/TD]
[TD="class: xl65, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DCE6F1]#DCE6F1[/URL] "]W2[/TD]
[/TR]
[TR]
[TD="class: xl69, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DCE6F1]#DCE6F1[/URL] "]Interior Doors[/TD]
[TD="class: xl69, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DCE6F1]#DCE6F1[/URL] "] [/TD]
[TD="class: xl69, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DCE6F1]#DCE6F1[/URL] "]ESTIMATE[/TD]
[TD="class: xl69, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DCE6F1]#DCE6F1[/URL] "]Charlie[/TD]
[TD="class: xl70, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DCE6F1]#DCE6F1[/URL] , align: right"]12/06/18[/TD]
[TD="class: xl70, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DCE6F1]#DCE6F1[/URL] , align: right"]26/06/18[/TD]
[TD="class: xl64, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DCE6F1]#DCE6F1[/URL] , align: right"]11[/TD]
[TD="class: xl65, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DCE6F1]#DCE6F1[/URL] "]W4[/TD]
[/TR]
[TR]
[TD="class: xl71"]Planters[/TD]
[TD="class: xl71"] [/TD]
[TD="class: xl71"]ESIMATE[/TD]
[TD="class: xl71"]Gary[/TD]
[TD="class: xl72, align: right"]07/05/18[/TD]
[TD="class: xl72, align: right"]30/07/18[/TD]
[TD="class: xl73, align: right"]61[/TD]
[TD="class: xl65, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DCE6F1]#DCE6F1[/URL] "]W7,W8[/TD]
[/TR]
[TR]
[TD="class: xl69, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DCE6F1]#DCE6F1[/URL] "]Window Frames[/TD]
[TD="class: xl69, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DCE6F1]#DCE6F1[/URL] "] [/TD]
[TD="class: xl69, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DCE6F1]#DCE6F1[/URL] "]CONFIRMED[/TD]
[TD="class: xl69, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DCE6F1]#DCE6F1[/URL] "]Gary[/TD]
[TD="class: xl70, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DCE6F1]#DCE6F1[/URL] , align: right"]07/05/18[/TD]
[TD="class: xl70, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DCE6F1]#DCE6F1[/URL] , align: right"]16/07/18[/TD]
[TD="class: xl64, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DCE6F1]#DCE6F1[/URL] , align: right"]51[/TD]
[TD="class: xl65, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DCE6F1]#DCE6F1[/URL] "]W2[/TD]
[/TR]
[TR]
[TD="class: xl71"]External Doors[/TD]
[TD="class: xl71"] [/TD]
[TD="class: xl71"]CONFIRMED[/TD]
[TD="class: xl71"]Jane[/TD]
[TD="class: xl72, align: right"]07/05/18[/TD]
[TD="class: xl72, align: right"]16/07/18[/TD]
[TD="class: xl73, align: right"]51[/TD]
[TD="class: xl65, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DCE6F1]#DCE6F1[/URL] "]W3[/TD]
[/TR]
[TR]
[TD="class: xl69, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DCE6F1]#DCE6F1[/URL] "]Hoarding[/TD]
[TD="class: xl69, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DCE6F1]#DCE6F1[/URL] "] [/TD]
[TD="class: xl69, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DCE6F1]#DCE6F1[/URL] "]CONFIRMED[/TD]
[TD="class: xl71"]Jane[/TD]
[TD="class: xl70, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DCE6F1]#DCE6F1[/URL] , align: right"]11/06/18[/TD]
[TD="class: xl70, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DCE6F1]#DCE6F1[/URL] , align: right"]30/07/18[/TD]
[TD="class: xl64, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DCE6F1]#DCE6F1[/URL] , align: right"]36[/TD]
[TD="class: xl65, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DCE6F1]#DCE6F1[/URL] "]W8[/TD]
[/TR]
[TR]
[TD="class: xl71"]Shelving Unit[/TD]
[TD="class: xl71"] [/TD]
[TD="class: xl71"]ESTIMATE[/TD]
[TD="class: xl71"]Jane[/TD]
[TD="class: xl72, align: right"]11/06/18[/TD]
[TD="class: xl72, align: right"]30/07/18[/TD]
[TD="class: xl73, align: right"]36[/TD]
[TD="class: xl65, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DCE6F1]#DCE6F1[/URL] "]W8[/TD]
[/TR]
[TR]
[TD="class: xl69, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DCE6F1]#DCE6F1[/URL] "]Timber Frame[/TD]
[TD="class: xl69, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DCE6F1]#DCE6F1[/URL] "] [/TD]
[TD="class: xl69, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DCE6F1]#DCE6F1[/URL] "]ESTIMATE[/TD]
[TD="class: xl71"]Philip[/TD]
[TD="class: xl70, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DCE6F1]#DCE6F1[/URL] , align: right"]23/07/18[/TD]
[TD="class: xl70, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DCE6F1]#DCE6F1[/URL] , align: right"]10/09/18[/TD]
[TD="class: xl64, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DCE6F1]#DCE6F1[/URL] , align: right"]36[/TD]
[TD="class: xl65, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DCE6F1]#DCE6F1[/URL] "]W08,W09,W10[/TD]
[/TR]
[TR]
[TD="class: xl71"]Dining Table[/TD]
[TD="class: xl71"] [/TD]
[TD="class: xl71"]ESTIMATE[/TD]
[TD="class: xl71"]Philip[/TD]
[TD="class: xl72, align: right"]23/07/18[/TD]
[TD="class: xl72, align: right"]10/09/18[/TD]
[TD="class: xl73, align: right"]36[/TD]
[TD="class: xl65, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DCE6F1]#DCE6F1[/URL] "]W9[/TD]
[/TR]
[TR]
[TD="class: xl69, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DCE6F1]#DCE6F1[/URL] "]Garden Furniture[/TD]
[TD="class: xl69, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DCE6F1]#DCE6F1[/URL] "] [/TD]
[TD="class: xl69, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DCE6F1]#DCE6F1[/URL] "]ESTIMATE[/TD]
[TD="class: xl71"]Philip[/TD]
[TD="class: xl70, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DCE6F1]#DCE6F1[/URL] , align: right"]25/06/18[/TD]
[TD="class: xl70, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DCE6F1]#DCE6F1[/URL] , align: right"]23/07/18[/TD]
[TD="class: xl64, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DCE6F1]#DCE6F1[/URL] , align: right"]21[/TD]
[TD="class: xl65, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DCE6F1]#DCE6F1[/URL] "]W5[/TD]
[/TR]
</tbody>[/TABLE]
The important data in columns "start date", "finish date" and "bay ref" as I want the information to appear in another sheet, that looks like this:
[TABLE="width: 1079"]
<colgroup><col width="78" style="width: 78pt;"><col width="65" span="2" style="width: 65pt;"><col width="91" style="width: 91pt;"><col width="65" span="12" style="width: 65pt;"></colgroup><tbody>[TR]
[TD="class: xl63, width: 78"] [/TD]
[TD="class: xl64, width: 65, align: right"]07/05/18[/TD]
[TD="class: xl64, width: 65, align: right"]08/05/18[/TD]
[TD="class: xl64, width: 91, align: right"]09/05/18[/TD]
[TD="class: xl64, width: 65, align: right"]10/05/18[/TD]
[TD="class: xl64, width: 65, align: right"]11/05/18[/TD]
[TD="class: xl64, width: 65, align: right"]12/05/18[/TD]
[TD="class: xl64, width: 65, align: right"]13/05/18[/TD]
[TD="class: xl64, width: 65, align: right"]14/05/18[/TD]
[TD="class: xl64, width: 65, align: right"]15/05/18[/TD]
[TD="class: xl64, width: 65, align: right"]16/05/18[/TD]
[TD="class: xl64, width: 65, align: right"]17/05/18[/TD]
[TD="class: xl64, width: 65, align: right"]18/05/18[/TD]
[TD="class: xl64, width: 65, align: right"]19/05/18[/TD]
[TD="class: xl64, width: 65, align: right"]20/05/18[/TD]
[TD="class: xl64, width: 65, align: right"]21/05/18[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DCE6F1]#DCE6F1[/URL] "]BAY W1[/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DCE6F1]#DCE6F1[/URL] "]BAY W2[/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DCE6F1]#DCE6F1[/URL] "]BAY W3[/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DCE6F1]#DCE6F1[/URL] "]BAY W4[/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DCE6F1]#DCE6F1[/URL] "]BAY W5[/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DCE6F1]#DCE6F1[/URL] "]BAY W6[/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DCE6F1]#DCE6F1[/URL] "]BAY W7[/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DCE6F1]#DCE6F1[/URL] "]BAY W8[/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DCE6F1]#DCE6F1[/URL] "]BAY W9[/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DCE6F1]#DCE6F1[/URL] "]BAY W10[/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[/TR]
</tbody>[/TABLE]
I would like a conditional formatting equation which would highlight a bay, when it is scheduled to be used that week so at a glance, I know what areas are busy and when. The added issue is that there might be multiple bays appearing in the cell in sheet 1, which should be separated and shown as in use according to the start/end date.
I think I need something that says: if the row header "BAY W*" appears in Bay Ref column of table 1, and the column header "date" is greater than "start date" and less than "end date" in sheet 1 then highlight the cell. But, Excel2010 doesn't understand my primitive language!...If anybody can advise, it would be much appreciated
Thanks