IF, AND, VLOOKUP into the future...

[Humber]

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:

PROJECT	CLIENT	STATUS	PROJECT MANAGER	START	END	DAYS	BAY REF
Tree House		CONFIRMED	Charlie	07/06/18	23/07/18	33	W1
Decking		CONFIRMED	Charlie	12/06/18	17/06/18	4	W2
Interior Doors		ESTIMATE	Charlie	12/06/18	26/06/18	11	W4
Planters		ESIMATE	Gary	07/05/18	30/07/18	61	W7,W8
Window Frames		CONFIRMED	Gary	07/05/18	16/07/18	51	W2
External Doors		CONFIRMED	Jane	07/05/18	16/07/18	51	W3
Hoarding		CONFIRMED	Jane	11/06/18	30/07/18	36	W8
Shelving Unit		ESTIMATE	Jane	11/06/18	30/07/18	36	W8
Timber Frame		ESTIMATE	Philip	23/07/18	10/09/18	36	W08,W09,W10
Dining Table		ESTIMATE	Philip	23/07/18	10/09/18	36	W9
Garden Furniture		ESTIMATE	Philip	25/06/18	23/07/18	21	W5

<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>
</tbody>

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:

07/05/18

08/05/18

09/05/18

10/05/18

11/05/18

12/05/18

13/05/18

14/05/18

15/05/18

16/05/18

17/05/18

18/05/18

19/05/18

20/05/18

21/05/18

BAY W1

BAY W2

BAY W3

BAY W4

BAY W5

BAY W6

BAY W7

BAY W8

BAY W9

BAY W10

<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>
</tbody>

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

 

[Peter_SSs]

Welcome to the MrExcel board!

I would like a conditional formatting equation which would highlight a bay, when it is scheduled to be used that week ..

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
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1	PROJECT	CLIENT	STATUS	PROJECT MANAGER	START	END	DAYS	BAY REF
2	Tree House		CONFIRMED	Charlie	20/05/2018	23/07/2018	33	W8
3	Decking		CONFIRMED	Charlie	12/06/2018	17/06/2018	4	W2
4	Interior Doors		ESTIMATE	Charlie	12/06/2018	26/06/2018	11	W4
5	Planters		ESIMATE	Gary	7/05/2018	18/05/2018	61	W7,W8
6	Window Frames		CONFIRMED	Gary	16/05/2018	20/05/2018	51	W5
7	External Doors		CONFIRMED	Jane	7/05/2018	16/07/2018	51	W3
8	Hoarding		CONFIRMED	Jane	11/06/2018	30/07/2018	36	W8
9	Shelving Unit		ESTIMATE	Jane	11/06/2018	30/07/2018	36	W8
10	Timber Frame		ESTIMATE	Philip	23/07/2018	10/09/2018	36	W08,W09,W10
11	Dining Table		ESTIMATE	Philip	23/07/2018	10/09/2018	36	W9
12	Garden Furniture		ESTIMATE	Philip	9/05/2018	12/05/2018	21	W5
13
14
15	BAY	7/05/18	8/05/18	9/05/18	10/05/18	11/05/18	12/05/18	13/05/18	14/05/18	15/05/18	16/05/18	17/05/18	18/05/18	19/05/18	20/05/18	21/05/18
16	W1
17	W2
18	W3
19	W4
20	W5
21	W6
22	W7
23	W8
Sheet1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B16	1. / 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?

 

[Humber]

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!!

 

[Peter_SSs]

Thanks so much, Peter!

You're welcome.

Hopefully there is a simple adjustment to automatically search an expanding range in the source table.

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
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1	BAY	7/05/18	8/05/18	9/05/18	10/05/18	11/05/18	12/05/18	13/05/18	14/05/18	15/05/18	16/05/18	17/05/18	18/05/18	19/05/18	20/05/18	21/05/18
2	W1
3	W2
4	W3
5	W4
6	W5
7	W6
8	W7
9	W8
10	W9
11	W10
Sheet2

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B2	1. / 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
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1	BAY	7/05/18	8/05/18	9/05/18	10/05/18	11/05/18	12/05/18	13/05/18	14/05/18	15/05/18	16/05/18	17/05/18	18/05/18	19/05/18	20/05/18	21/05/18
2	W1
3	W2
4	W3
5	W4
6	W5
7	W6
8	W7
9	W8
10	W9
11	W10
Sheet2

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B2:P11	Expression	=SUMPRODUCT(--(ISNUMBER(SEARCH(","&$A2&",",","&INDIRECT("Projects[BAY REF]")&","))),--(B$1>=INDIRECT("Projects[START]")),--(B$1<=INDIRECT("Projects[END]")))	text	NO