Output and answer based upon product length and sizes

[Andrew Watson]

So I have a spreadsheet over 2 sheets and Sheet 1 has an 'order form' and the second sheet has all of my drop down and VLOOKUP information. I'm trying to determine what formula to use as I want to minimise the amount of data entry required to complete the task.

I manually enter the data in for the qty, length and size of the rope on sheet 1. I also have a dependent drop down list which list the different construction ropes for each size (not shown on the below and thanks to the posts here I can now do these). I then want it calculate the size of the reel required which is based upon the length generally, but also size in larger diametres and lengths. It would then need to determine the qty of pallets required based upon the total qty and size of the reels. I've looked at lots of posts and have become suitably confused with what may or may not work so I thought I ask the brains trust here. I have my rope lengths etc on sheet 2. I would try and post up a XL2BB sheet but I don't know if it upload both tabs. I tried using the test area to try my sample file but whilst I could upload it I could not get the file I attached to open so the picture below is the best I can do for the moment.

 

[Zot]

Just make 2 captures. One for each sheet. This way your formula can be seen and those helpers here can get more idea from formula perhaps.

 

[Andrew Watson]

Does this work?

M18-03-00 Mr Excel version..xlsx
	A	B	C	D	E		G	H	I	J	K	L
1
2
3
4	Commodity Code	QTY.	LENGTH metres	TOTAL metres	DIA. mm			$ per m	TOTAL $	REEL SIZE	No. of REELS	TOTAL $ + REEL CHARGE
5
6	HOISTWAY	6	145	870	16		16mm 8X19S	11	$9,570.00
7	SERVICE HOIST			0
8	COMPENSATION			0
9	GOVERNOR	1	221	221	12.7		12.7mm 8X19S	7	$1,547.00
10
11
12	WOODEN PALLET						8 sm reels to the pallet		$15.00		sm reel $22	$0.00
13							6 lge reels to the pallet				lge reel $37.60
14												$154.00
15	Requested By : Date :	2021-07-16
16											GST	$15.40
17											Total	$169.40
Sheet1

Cell Formulas
Range	Formula
H6:H11	H6	=IFERROR(VLOOKUP(G6,'Rope Specs'!D21:E33,2,FALSE),"")
I6:I11	I6	=IFERROR((H6*D6),"")
D6:D9	D6	=B6*C6

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
D6:D11	Cell Value	=0	text	NO

Cells with Data Validation
Cell	Allow	Criteria
G6	List	=INDIRECT($F$6)
G7	List	=INDIRECT($F$7)
G8	List	=INDIRECT($F$8)
G9	List	=INDIRECT($F$9)
E6:E9	List	='Rope Specs'!$A$3:$A$13

M18-03-00 Mr Excel version..xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M
2	Rope Diameter			sixfive	eight	ninefive	ten	eleven	twelve	twelveseven	thirteen	fouteeen	sixteen
3
4	6.5	sixfive		6.5mm 6X19S	8mm 8X19S	9.5mm Governor 8X19S	10mm 8X19W B1570	11mm 8X19S	12mm 8X19S	12.7mm 8X19S	13mm 8X19S	14mm 8X19S	16mm 8X19S
5	8	eight									13mm DRAKA		16mm EHS 8X19S
6	9.5	ninefive											16mm DRAKA 8X19S
7	10	ten
8	11	eleven
9	12	twelve
10	12.7	twelveseven
11	13	thirteen
12	14	fourteen
13	16	sixteen
14
15
16
17
18
19
20				Rope Spec	Price per mtr
21				6.5mm 6X19S	$1.00
22				8mm 8X19S	$2.00
23				9.5mm Governor 8X19S	$3.00
24				10mm 8X19W B1570	$4.00
25				11mm 8X19S	$5.00
26				12mm 8X19S	$6.00
27				12.7mm 8X19S	$7.00
28				13mm 8X19S	$8.00
29				13mm DRAKA	$9.00
30				14mm 8X19S	$10.00
31				16mm 8X19S	$11.00
32				16mm EHS 8X19S	$12.00
33				16mm DRAKA 8X19S	$13.00
34
35
36
37				Small reels (if rope < 139m) 8 to the pallet
38
39				Large reels (if rope >140m up to 300m) 6 to the pallet
40
41				Larger reels (if rope >300m 16mm or >200m 18mm) 6 to the pallet
42
43				Wooden Pallet
44
45
46
47				Small	$10.00		<139
48				Large	$11.00		>140 <300
49				Larger	$12.00		Greater than 300mtrs of 16mm Rope or 20mtrs of 18mm Rope
50				Wooden Pallet	$13.00
Rope Specs

 

[Zot]

Looks ok so far but I have tons of works now to really understand this. Hope someone can help sooner.

 

[Zot]

So you want to fill column J and K automatically? The reel size I can understand but how do you determine how many reel?
If you have >300m, will you have combination of larger and large or small reel?

Not in rope business. So, not really understand everything here ?

What is the expected answer in column J and K?

 

[Andrew Watson]

So you want to fill column J and K automatically? The reel size I can understand but how do you determine how many reel?
If you have >300m, will you have combination of larger and large or small reel?

Not in rope business. So, not really understand everything here ?

What is the expected answer in column J and K?

Hi Zot thanks for the reply.

The output data should look like the below and columns J & K should automatically fill out. It should be able to determine what size reel and the qty required by the length and dia. hence why I split these fields out on the spreadsheet. The qty of reels will never exceed the number of rope lengths required

Where there is more than 300mtrs of 16mm dia. rope then it will all fit onto a larger reel as the rope can only be placed on one reel and the output data would look like the below.

If the lengths of rope are much shorter they would then be placed on a small reel.

Appreciate your help here if it's at all possible.

 

[Zot]

I wonder why for 3rd example the Reel Size is Large.

 

[Zot]

The No of Reel seems to follow the Qty in column B, right?