Conditional formatting using Vlookup and/or INDIRECT functions

[MrPeterified]

This is used for a scheduling tool that is in a workbook, the workbook has an sheet called IW49N Download which is a data dump from a program called SAP, it then has 7 other sheets 1 for each day of the week. Below I am explaining a problem I have with the sheets for each day of the week.

I have a list of names in 1 column. In front of each name is a row of 24 cells. Each cell represents an hour of the day. The 24 cells are conditionally formatted to be grey when a name is unavailable for a specific day or set of hours in that day. The conditionally formatted cells would not copy and paste so you'll have to imagine 24 cells after Name1, Name2, Name3 etc. The conditional formatting is based on the 3 columns of numbers that are after the 24 conditionally formatted cells you cannot see. For Name1 the 7 means the employee starts at 7AM, the 8 means the employee works for 8 hours and the 15 means the employee ends his shift at 3PM. Likewise for Name3 the employee starts at 11PM works for 8 hours and is done at 7AM, Name6 the employee starts the shift at 3PM, works 8hours and is finished at 11PM.

Name1 7 8 15
Name2 7 8 15
Name3 23 8 7
Name4 7 8 15
Name5 7 8 15
Name6 15 8 23

The values in the 3 columns are found by the following function/code:
Column 1 (start time of shift) =IF(ISNA(VLOOKUP($C146,'Schedule WK 46.xlsm'!Schedule,11,FALSE)),0,VLOOKUP($C146,'Schedule WK 46.xlsm'!Schedule,11,FALSE))
Column 2 (duration of shift) =IF(ISNA(VLOOKUP($C146,'Schedule WK 46.xlsm'!Schedule,10,FALSE)),0,VLOOKUP($C146,'Schedule WK 46.xlsm'!Schedule,10,FALSE))
Column 3 (End of shift) =IF(AK146+AL146>24,AK146+AL146-24,AK146+AL146) This function converts the end time from a 12hour clock to a 24hour clock time
The array named schedule is found on a separate tab and the data in that tab is downloaded from another program.

The conditional formatting of the 24 cells after the names looks like this

Formula: =$AL146=0 Applies to =$L$146:$AI$170 Makes all 24 cells grey based on duration of shift, if shift is zero all cells are grey
=$AK146=23 Applies to =$L$146:$AA$170 If start of shift is 11PM or 2300hrs then make the first 16 cells grey
=$AK146=15 Applies to =$AB$146:$AI$170,$L$146:$S$170 If start of shift is 3PM or 1500hrs then make the first 8 and last 8 cells grey leaving the middle 8 cells white
=$AK146=0 Applies to =$L$146:$AI$170 If start of shift is zero (vlookup cant find employees name from downloaded data) then make all 24 cells grey

The above gives me employee availability throughout the day.

On the same sheet I have 12 columns of importance

Operations Work Center Craftsman WO # E/I # E/I hr total MW # MW hr total PF # PF hr total
Instrument Tech Name1 200171648 0 0 0 0 0 0 0 0 0
Pipefitter Name2 200171648 0 0 0 0 0 0 0 0 0
Millwright Name3 200171648 0 0 0 0 0 0 0 0 0
Instrument Tech Name1 200171647 0 0 0 0 0 0 0 0 0
Pipefitter Name2 200171647 0 0 0 0 0 0 0 0 0
Millwright Name3 200171647 0 0 0 0 0 0 0 0 0

Under the E/I#, MW#, PF# columns I have the following formula
=IF(M12=0,"0",N12/M12)
This is used to calculate the number of Electrician/Instrumentation (E/I) craftsman that I need based on the next 2 columns

Under the E/I hr, MW hr, and PF hr columns I have the following formula
=SUMIFS('IW49N Download'!$I$7:$I$4954,'IW49N Download'!$A$7:$A$4954,$D12,'IW49N Download'!$C$7:$C$4954,"BNE_I")
=SUMIFS('IW49N Download'!$I$7:$I$4954,'IW49N Download'!$A$7:$A$4954,$D12,'IW49N Download'!$C$7:$C$4954,"BNMRWT")
=SUMIFS('IW49N Download'!$I$7:$I$4954,'IW49N Download'!$A$7:$A$4954,$D12,'IW49N Download'!$C$7:$C$4954,"BNPIPE")

Under the column labeled "total" for each craft I have
=SUMIFS('IW49N Download'!$J$7:$J$4954,'IW49N Download'!$A$7:$A$4954,$D12,'IW49N Download'!$C$7:$C$4954,"BNE_I")
=SUMIFS('IW49N Download'!$J$7:$J$4954,'IW49N Download'!$A$7:$A$4954,$D12,'IW49N Download'!$C$7:$C$4954,"BNMRWT")
=SUMIFS('IW49N Download'!$J$7:$J$4954,'IW49N Download'!$A$7:$A$4954,$D12,'IW49N Download'!$C$7:$C$4954,"BNPIPE")

In the above formulas, "IW49N Download" is a sheet that has data dumped into it from another program called SAP which is used to track specific maintenance jobs, equipment and parts charged to each job and the number of hours required for each craft on each job. With that said, the above formulas are only somewhat useful and I'll need to give an example to explain why. Below is 1 line of data that is dumped into the IW49N Download worksheet

Order Oper.Work Center Number Normal duration Total Man Hours
200172497 BNPIPE 3 4.0 12.0

What this says is Work Order Number 200172497 needs 3 Pipefitters (BNPIPE) each for 4 hours for a total of 12 hours
That means on the worksheet for Monday lets say, I need three rows each with the order number 200172497. The reason I need 3 rows of the same work order number is because I need to assign Name1, Name2, Name3 to that job. Since the data comes in like it does above then on the worksheet for the day of the week the formula
=SUMIFS('IW49N Download'!$I$7:$I$4954,'IW49N Download'!$A$7:$A$4954,$D12,'IW49N Download'!$C$7:$C$4954,"BNPIPE")
will only return the number 4. and
=SUMIFS('IW49N Download'!$J$7:$J$4954,'IW49N Download'!$A$7:$A$4954,$D12,'IW49N Download'!$C$7:$C$4954,"BNPIPE")
will only return the number 12, with those 2 numbers entered then
=IF(M12=0,"0",N12/M12)
will return the number 3

The point of the scheduling tool is to be able to schedule Name1 on multiple jobs to fill their 8 hour day. The point of the conditional formatting and what I would like it to be able to do is to conditionally format the 24 cells or 8 cells after their name based on the hours in the "Total" column. So if Name1 appears on the list 2 different times each for 4 hours the 8 cells after his name would be conditionally formatted and shaded in some different color than grey or white. This way I would be able to quickly tell Name1 has his day full and I will not schedule him for any more jobs. I have uploaded a file via dropbox public folder that has only 1 day of the week on it. I'm sorry for the extremely long winded explanation however I felt it was necessary to explain the problem
https://dl.dropboxusercontent.com/u/36150227/Biron Maintenance Scheduling rev22.xlsm

 

[RickXL]

If I have understood your question corretly, you want the cells from L150 to indicate assigned capacity?

If so then I think I have a formula that gets you there. I simplified things a bit and defined some Named Ranges for Name and Hours. The spreadsheet looks like this:

Excel 2010

	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH	AI
1	Name	Hours	Start					Name				7	8	9	10	11	12	13	14	15	16	17	18	19	20	21	22	23	24	1	2	3	4	5	6
2	aaa	8	7					aaa
3	bbb	8	8					bbb
4	ccc	7	8					ccc
5	ddd	3	10					ddd
6	ddd	4	10
7	ddd	3	10

<colgroup><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Worksheet Formulas Cell Formula A1 Name B1 Hours C1 Start D1 E1 F1 G1 H1 Name I1 J1 K1 L1 7 M1 8 N1 9 O1 10 P1 11 Q1 12 R1 13 S1 14 T1 15 U1 16 V1 17 W1 18 X1 19 Y1 20 Z1 21 AA1 22 AB1 23 AC1 24 AD1 1 AE1 2 AF1 3 AG1 4 AH1 5 AI1 6 A2 aaa B2 8 C2 7 D2 E2 F2 G2 H2 aaa I2 J2 K2 L2 M2 N2 O2 P2 Q2 R2 S2 T2 U2 V2 W2 X2 Y2 Z2 AA2 AB2 AC2 AD2 AE2 AF2 AG2 AH2 AI2 A3 bbb B3 8 C3 8 D3 E3 F3 G3 H3 bbb I3 J3 K3 L3 M3 N3 O3 P3 Q3 R3 S3 T3 U3 V3 W3 X3 Y3 Z3 AA3 AB3 AC3 AD3 AE3 AF3 AG3 AH3 AI3 A4 ccc B4 7 C4 8 D4 E4 F4 G4 H4 ccc I4 J4 K4 L4 M4 N4 O4 P4 Q4 R4 S4 T4 U4 V4 W4 X4 Y4 Z4 AA4 AB4 AC4 AD4 AE4 AF4 AG4 AH4 AI4 A5 ddd B5 3 C5 10 D5 E5 F5 G5 H5 ddd I5 J5 K5 L5 M5 N5 O5 P5 Q5 R5 S5 T5 U5 V5 W5 X5 Y5 Z5 AA5 AB5 AC5 AD5 AE5 AF5 AG5 AH5 AI5 A6 ddd B6 4 C6 10 D6 E6 F6 G6 H6 I6 J6 K6 L6 M6 N6 O6 P6 Q6 R6 S6 T6 U6 V6 W6 X6 Y6 Z6 AA6 AB6 AC6 AD6 AE6 AF6 AG6 AH6 AI6 A7 ddd B7 3 C7 10 D7 E7 F7 G7 H7 I7 J7 K7 L7 M7 N7 O7 P7 Q7 R7 S7 T7 U7 V7 W7 X7 Y7 Z7 AA7 AB7 AC7 AD7 AE7 AF7 AG7 AH7 AI7 <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

Workbook Defined Names Name Refers To Hours =OFFSET(Sheet1!$B$2,0,0,COUNTA(Sheet1!$B:$B)-1,1) Name =OFFSET(Sheet1!$A$2,0,0,COUNTA(Sheet1!$A:$A)-1,1) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

The Conditional Format looked like this:
=(SUMIF(Name,$H2,Hours)>0) * ((COLUMN(L$2)-5)>=($C2)) * ((COLUMN(L$2)-5)<($C2+SUMIF(Name,$H2,Hours)))

 

[RickXL]

I seem to have failed the upload a spreadsheet test so I am trying a bitmap ...