Calculating case quantities

[THurley]

I take inventory daily and want to be able to enter a number of pieces then the excel spreadsheet calculate into cases. Case quantities differ between each product for example 6 #10 cans equal 1 case or 24 boxes equal 1 case of cornstarch; can some one help me please? Thank you!!

 

[StephenCrump]

Welcome to the Forum!

Here are a couple of ideas that may help?

	A	B	C	D	E	F	G	H
1	Inventory					LookupTables
2
3	Product	Units	Cases			Product	CaseSize
4	Cornflakes 375g	15	1.25			Cornstarch	24
5	Cornstarch	25	1.04			#10 can	6
6	Biscuits	10	#N/A			Cornflakes 750g	6
7	Fruitcakes	8	0.40			Cornflakes 375g	12
8						Fruitcakes	20
9
10	OR ....
11				Cases
12	Product	Type	Units	(rounded up)		Product	Type	CaseSize
13	Cornflakes	375g pack	15	2		Cornstarch	Small packet	20
14	#10 can		25	4		#10 can		8
15	Biscuits		8	#N/A		Cornflakes	750g pack	6
16	Fruitcakes	Large	8	1		Cornflakes	375g pack	12
17						Fruitcakes	Large	18
18
Sheet1

Cell Formulas
Range	Formula
C4:C7	C4	=B4/XLOOKUP(A4,Product,CaseSize)
D13:D16	D13	=ROUNDUP(C13/XLOOKUP(1,(Product1=A13)*(Type1=B13),CaseSize1),0)

Named Ranges
Name	Refers To	Cells
CaseSize	=Sheet1!$G$4:$G$8	C4:C7
CaseSize1	=Sheet1!$H$13:$H$17	D13:D16
Product	=Sheet1!$F$4:$F$8	C4:C7
Product1	=Sheet1!$F$13:$F$17	D13:D16
Type1	=Sheet1!$G$13:$G$17	D13:D16

 

[THurley]

Mr. Crump I have attaching a pic of the spread sheet I started that I am trying to incorporate your work if you could look at it? I am not great at excell but I can perform basic stuff. Please see attachment if that give a better idea what i am attempting to do. Thank you so much for your help.

 

[StephenCrump]

I'm not sure there's a lot we can add, based on just a picture.

Is Post #2 on the right track?

If so, perhaps you could try to implement an XLOOKUP using your layout, and post back if you are having problems?

It's best if you can use XL2BB to post (as I did in Post #2), so that we don't need to retype your data and formulae.