Conditional Formatting

[leebauman]

I have an daily inventory record which shows daily sales quantities and inventory replenishments.

When daily inventory reduces to less than or equal to (8), replenishment occurs adding (8) more to inventory.

The replenishment process takes (14) days though, and I want to indicate the day replenishment starts by yellow highlighting the cell.

I've tried conditional formatting but having trouble. Appreciate any help. Thanks.

Small Program model.xlsx
	A	B	C	D	E	F	G	H	I
6	Day	Units sold	1	2	3	4	5	6	7
7	1	3	87
8	2	4	83
9	3	2	81
10	4	1	80
11	5	2	78
12	6	3	75
13	7	5	70
14	8	0	70
15	9	0	70
16	10	9	61
17	11	3	58
18	12	4	54
19	13	3	51
20	14	1	50
21	15	5	45
22	16	0	45
23	17	4	41
24	18	6	35
25	19	9	26
26	20	12	14
27	21	2	12
28	22	4	8	16
29	23	6	2	10
30	24	3	-1	7	15
31	25	2	-3	5	13
32	26	1	-4	4	12
33	27	1	-5	3	11
34	28	3	-8	0	8	16
35	29	6	-14	-6	2	10
36	30	8	-22	-14	-6	2	10
37	31	3	-25	-17	-9	-1	7	15
38	32	4	-29	-21	-13	-5	3	11
39	33	6	-35	-27	-19	-11	-3	5	13
Sheet1

Cell Formulas
Range	Formula
C7	C7	=B4-B7
D7:I39	D7	=IF(C7<=$B$3,C7+$B$3,"")
C8	C8	=IF(C7-B8>B3,C7-B8,"")
C9:C39	C9	=C8-B9
A9:A39	A9	=A8+1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
E7:L453	Cell Value	<$B$3	text	NO
E7:L453	Cell Value	<8	text	NO
C7:D1048576	Cell Value	<$B$3	text	NO

 

[awoohaw]

It seems you have data above Row 6 that is needed in for the calculations. Please repost the xl2bb mini sheet.
Additionally, it seems that some of the formulas are inconsistent (different beginning C6 to I39).

 

[leebauman]

Apologies. Here is the full table

Small Program model.xlsx
	A	B	C	D	E	F	G	H	I
1	Days to procure	14
2	AVG procure period usage	40
3	Min procure Qty	8
4	Original inventory	90
5
6	Day	Units sold	1	2	3	4	5	6	7
7	1	3	87
8	2	4	83
9	3	2	81
10	4	1	80
11	5	2	78
12	6	3	75
13	7	5	70
14	8	0	70
15	9	0	70
16	10	9	61
17	11	3	58
18	12	4	54
19	13	3	51
20	14	1	50
21	15	5	45
22	16	0	45
23	17	4	41
24	18	6	35
25	19	9	26
26	20	12	14
27	21	2	12
28	22	4	8	16
29	23	6	2	10
30	24	3	-1	7	15
31	25	2	-3	5	13
32	26	1	-4	4	12
33	27	1	-5	3	11
34	28	3	-8	0	8	16
35	29	6	-14	-6	2	10
36	30	8	-22	-14	-6	2	10
37	31	3	-25	-17	-9	-1	7	15
38	32	4	-29	-21	-13	-5	3	11
39	33	6	-35	-27	-19	-11	-3	5	13
Sheet1

Cell Formulas
Range	Formula
B2	B2	=SUM(B7:B20)
C7	C7	=B4-B7
D7:I39	D7	=IF(C7<=$B$3,C7+$B$3,"")
C8	C8	=IF(C7-B8>B3,C7-B8,"")
C9:C39	C9	=C8-B9
A9:A39	A9	=A8+1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
E7:L453	Cell Value	<$B$3	text	NO
E7:L453	Cell Value	<8	text	NO
C7:D1048576	Cell Value	<$B$3	text	NO