IF, AND, VLOOKUP into the future...

H

Humber

New Member
Joined
May 12, 2018
Messages
2
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...:confused:

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
 

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.
Sort by date Sort by votes
Welcome to the MrExcel board!

Humber said:
I would like a conditional formatting equation which would highlight a bay, when it is scheduled to be used that week ..
Click to expand...
See if you can make use of this. Note that I have altered some of the sample data in the top table to give a bit more variety in the results table.
For this example, I selected from B16:P25 and applied the Conditional Formatting (use a formula..) shown.

Excel Workbook
ABCDEFGHIJKLMNOP
1PROJECTCLIENTSTATUSPROJECT MANAGERSTARTENDDAYSBAY REF
2Tree HouseCONFIRMEDCharlie20/05/201823/07/201833W8
3DeckingCONFIRMEDCharlie12/06/201817/06/20184W2
4Interior DoorsESTIMATECharlie12/06/201826/06/201811W4
5PlantersESIMATEGary7/05/201818/05/201861W7,W8
6Window FramesCONFIRMEDGary16/05/201820/05/201851W5
7External DoorsCONFIRMEDJane7/05/201816/07/201851W3
8HoardingCONFIRMEDJane11/06/201830/07/201836W8
9Shelving UnitESTIMATEJane11/06/201830/07/201836W8
10Timber FrameESTIMATEPhilip23/07/201810/09/201836W08,W09,W10
11Dining TableESTIMATEPhilip23/07/201810/09/201836W9
12Garden FurnitureESTIMATEPhilip9/05/201812/05/201821W5
13
14
15BAY7/05/188/05/189/05/1810/05/1811/05/1812/05/1813/05/1814/05/1815/05/1816/05/1817/05/1818/05/1819/05/1820/05/1821/05/18
16W1
17W2
18W3
19W4
20W5
21W6
22W7
23W8
Sheet1
Cells with Conditional Formatting
CellConditionCell FormatStop If True
B161. / Formula is =SUMPRODUCT(--(ISNUMBER(SEARCH(","&$A16&",",","&$H$2:$H$12&","))),--(B$15>=$E$2:$E$12),--(B$15<=$F$2:$F$12))Abc



Just one further note: You will need to be consistent with your Bay references. For example, in the top table you have used both W8 and W08 which I assume are the same bays?
 
Last edited:
Upvote 0
Thanks so much, Peter! I've been trying out/learning different formulas but hadn't got any where near - it's a great help. My sheet is basic but at least this means it will work and now I can develop it further. Noted re. bay refs. Hopefully there is a simple adjustment to automatically search an expanding range in the source table.

Thanks!!:)
 
Upvote 0
Humber said:
Thanks so much, Peter!
Click to expand...
You're welcome. :)


Humber said:
Hopefully there is a simple adjustment to automatically search an expanding range in the source table.
Click to expand...
Obviously you would need to move the lower section away from beneath the source table. That could be over to the right or to another worksheet. For my examples below, I have moved it to Sheet2. So, my Sheet1 now just contains the data from rows 1:12 above. Here are two methods that could then be used to cope with an expanding source range.

A) Just make the ranges of the CF formula that refer to the source data big enough to cover however big you think the source data might get. In this example, I have used down to row 100, but that could be 1000 or whatever you choose.

Excel Workbook
ABCDEFGHIJKLMNOP
1BAY7/05/188/05/189/05/1810/05/1811/05/1812/05/1813/05/1814/05/1815/05/1816/05/1817/05/1818/05/1819/05/1820/05/1821/05/18
2W1
3W2
4W3
5W4
6W5
7W6
8W7
9W8
10W9
11W10
Sheet2
Cells with Conditional Formatting
CellConditionCell FormatStop If True
B21. / Formula is =SUMPRODUCT(--(ISNUMBER(SEARCH(","&$A2&",",","&Sheet1!$H$2:$H$100&","))),--(B$1>=Sheet1!$E$2:$E$100),--(B$1<=Sheet1!$F$2:$F$100))Abc



B) On Sheet1, convert the source data to a formal table. Select the data (A1:H12 in the sample) then on the Insert ribbon tab -> Table -> My Table has headers -> OK. That will create a table called Table1. To make it more meaningful, go to the Formulas ribbon tab -> Name Manager -> Select Table1 -> Edit... -> change its Name: to Projects -> OK -> Close

Now on Sheet2, set up this CF. Whenever the 'Projects' table is altered (eg new rows added), the Sheet2 CF should automatically update to reflect that changed/added data.


Test 5.xlsm
ABCDEFGHIJKLMNOP
1BAY7/05/188/05/189/05/1810/05/1811/05/1812/05/1813/05/1814/05/1815/05/1816/05/1817/05/1818/05/1819/05/1820/05/1821/05/18
2W1
3W2
4W3
5W4
6W5
7W6
8W7
9W8
10W9
11W10
Sheet2
Cells with Conditional Formatting
CellConditionCell FormatStop If True
B2:P11Expression=SUMPRODUCT(--(ISNUMBER(SEARCH(","&$A2&",",","&INDIRECT("Projects[BAY REF]")&","))),--(B$1>=INDIRECT("Projects[START]")),--(B$1<=INDIRECT("Projects[END]")))textNO
 
Last edited:
Upvote 0
You must log in or register to reply here.

Similar threads

A
  • Question Question
Find entries in a sheet available or not
2
Replies
10
Views
494
AFZAL SOHAIL
A
SaraWitch
  • Solved
VLOOKUP formula with multiple IF statements
Replies
2
Views
909
SaraWitch
SaraWitch
D
  • Solved
VBA Function to Paste Table into Outlook Email Resulting in HTML language in Email
Replies
8
Views
645
smozgur
smozgur
S
  • Question Question
Create New Sheet with Specific Name Based on Variable Cell
Replies
0
Views
242
SBExcel123
S
M
  • Question Question
Help with FilterXML formula
Replies
0
Views
2K
montex01
M

Forum statistics

Threads
1,224,822
Messages
6,181,165
Members
453,021
Latest member
Justyna P

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top