# Output and answer based upon product length and sizes

#### Andrew Watson

##### New Member
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.

### Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.

#### Zot

##### Well-known Member
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

##### New Member
Does this work?
M18-03-00 Mr Excel version..xlsx
ABCDEGHIJKL
1
2
3
4Commodity CodeQTY.LENGTH metresTOTAL metresDIA. mm\$ per mTOTAL \$REEL SIZENo. of REELSTOTAL \$ + REEL CHARGE
5
6HOISTWAY61458701616mm 8X19S11\$9,570.00
7SERVICE HOIST0
8COMPENSATION0
9GOVERNOR122122112.712.7mm 8X19S7\$1,547.00
10
11
12WOODEN PALLET8 sm reels to the pallet\$15.00sm reel \$22\$0.00
136 lge reels to the palletlge reel \$37.60
14\$154.00
15Requested By : Date :2021-07-16
16GST\$15.40
17Total\$169.40
Sheet1
Cell Formulas
RangeFormula
H6:H11H6=IFERROR(VLOOKUP(G6,'Rope Specs'!D21:E33,2,FALSE),"")
I6:I11I6=IFERROR((H6*D6),"")
D6:D9D6=B6*C6
Cells with Conditional Formatting
CellConditionCell FormatStop If True
D6:D11Cell Value=0textNO
Cells with Data Validation
CellAllowCriteria
G6List=INDIRECT(\$F\$6)
G7List=INDIRECT(\$F\$7)
G8List=INDIRECT(\$F\$8)
G9List=INDIRECT(\$F\$9)
E6:E9List='Rope Specs'!\$A\$3:\$A\$13

M18-03-00 Mr Excel version..xlsx
ABCDEFGHIJKLM
2Rope Diametersixfiveeightninefiveteneleventwelvetwelveseventhirteenfouteeensixteen
3
46.5sixfive6.5mm 6X19S8mm 8X19S9.5mm Governor 8X19S10mm 8X19W B157011mm 8X19S12mm 8X19S12.7mm 8X19S13mm 8X19S14mm 8X19S16mm 8X19S
58eight13mm DRAKA16mm EHS 8X19S
69.5ninefive16mm DRAKA 8X19S
710ten
811eleven
912twelve
1012.7twelveseven
1113thirteen
1214fourteen
1316sixteen
14
15
16
17
18
19
20Rope SpecPrice per mtr
216.5mm 6X19S\$1.00
228mm 8X19S\$2.00
239.5mm Governor 8X19S\$3.00
2410mm 8X19W B1570\$4.00
2511mm 8X19S\$5.00
2612mm 8X19S\$6.00
2712.7mm 8X19S\$7.00
2813mm 8X19S\$8.00
2913mm DRAKA\$9.00
3014mm 8X19S\$10.00
3116mm 8X19S\$11.00
3216mm EHS 8X19S \$12.00
3316mm DRAKA 8X19S\$13.00
34
35
36
37Small reels (if rope < 139m) 8 to the pallet
38
39Large reels (if rope >140m up to 300m) 6 to the pallet
40
41Larger reels (if rope >300m 16mm or >200m 18mm) 6 to the pallet
42
43Wooden Pallet
44
45
46
47Small\$10.00<139
48Large\$11.00>140 <300
49Larger\$12.00Greater than 300mtrs of 16mm Rope or 20mtrs of 18mm Rope
50Wooden Pallet\$13.00
Rope Specs

#### Zot

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

#### Zot

##### Well-known Member
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

##### New Member
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

##### Well-known Member
I wonder why for 3rd example the Reel Size is Large.

#### Zot

##### Well-known Member
The No of Reel seems to follow the Qty in column B, right?

Replies
1
Views
54
Replies
0
Views
258
Replies
0
Views
232
Replies
1
Views
187
Replies
1
Views
251

1,181,590
Messages
5,930,774
Members
436,760
Latest member
robgreen43

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back