Help with a formula

[Torchwood72]

Hi
I have a consumable tracker that gets updated each week when they are counted, i have been asked to store each weeks counts onto sheet1 based on the date, but whenever i change the date the data disappears. I need to keep the unit count for each so we can charge the customer.

 

[GraH]

It's hard to help when we can't see all the data and no formulas.
Pictures are not so helpful. You maybe can share a sanitized sample data set via the XL2BB add-on. Link is in my signature.

 

[Torchwood72]

It's hard to help when we can't see all the data and no formulas.
Pictures are not so helpful. You maybe can share a sanitized sample data set via the XL2BB add-on. Link is in my signature.

Consumable Tracker 11.xlsm
	D	E	F	G
4	Description	Customer to be charged	Date	Weekly Stock Count (Units)
5	Cardboard Packing Box Example	Customer 1	26/03/2024	1327
6	Cardboard Packing Box Example	Customer 2	26/03/2025	260
7	Parcel	Customer 1	24/03/2024	69
8	Parcel	Customer 2
sheet1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B5:N22	Expression	=#REF!="Yes"	text	NO
B5:N22	Expression	=$L5=1	text	NO
E5:E22	Other Type	Icon set		NO

Consumable Tracker 11.xlsm
	B	C	D	E	F	G	H
2	Inventory ID	Description	Customer to be charged	Weekly Stock Count (Units)	Deliveries receieved this week in Units	Weekly Stock Count (Units)	Deliveries receieved this week in Units
3	Example	Cardboard Packing Box Example	Customer 1	1327	1200	1327	1200
4	Example	Cardboard Packing Box Example	Customer 2	260	250	260	250
5	IN0001	Parcel	Customer 1	69		0
6	IN0001	Parcel	Customer 2	69	0	0
7	IN0002	Cardboard	Customer 1
8	IN0002	Cardboard	Customer 2
Sheet2

Cell Formulas
Range	Formula
E5	E5	=IFERROR(IF(sheet1!F7=Sheet2!E1,sheet1!G7,0),0)
G5	G5	=IFERROR(IF(sheet1!F7=Sheet2!G1,sheet1!G7,0),0)
E6	E6	=IFERROR(IF(sheet1!F7=Sheet2!E1,sheet1!G7,0),0)
F6	F6	=IFERROR(IF(Sheet2!G7=Sheet2!F1,sheet1!H7,0),0)
G6	G6	=IFERROR(IF(sheet1!F7=Sheet2!G1,sheet1!G7,0),0)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
H3:H35	Expression	=#REF!="Yes"	text	NO
H3:H35	Expression	=$I5=1	text	NO
G6:G35,G3:G4	Expression	=#REF!="Yes"	text	NO
G6:G35,G3:G4	Expression	=$J3=1	text	NO
D3:D20	Expression	=#REF!="Yes"	text	NO
D3:D20	Expression	=$L3=1	text	NO
D3:D20	Other Type	Icon set		NO
F3:F5,F7:F117,E7	Expression	=#REF!="Yes"	text	NO
F3:F5,F7:F117,E7	Expression	=$I5=1	text	NO
B21:E117,B3:C20,E8:E20,E3:E4	Expression	=#REF!="Yes"	text	NO
B21:E117,B3:C20,E8:E20,E3:E4	Expression	=$J3=1	text	NO

Cells with Data Validation
Cell	Allow	Criteria
B2:B4	Any value
C2:C4	Any value
D2:D4	Any value
E2:E4	Any value
F2:F4	Any value
G2:G4	Any value
H2:H4	Any value