Output and answer based upon product length and sizes

Andrew Watson

New Member
Joined
Feb 3, 2016
Messages
10
Office Version
  1. 365
Platform
  1. Windows
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.

1616713216349.png
 

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.

Zot

Well-known Member
Joined
Nov 26, 2020
Messages
1,170
Office Version
  1. 2016
Platform
  1. Windows
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
Joined
Feb 3, 2016
Messages
10
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Nov 26, 2020
Messages
1,170
Office Version
  1. 2016
Platform
  1. Windows
Looks ok so far but I have tons of works now to really understand this. Hope someone can help sooner.
 

Zot

Well-known Member
Joined
Nov 26, 2020
Messages
1,170
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

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
Joined
Feb 3, 2016
Messages
10
Office Version
  1. 365
Platform
  1. Windows
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

1617838584201.png


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.

1617838788473.png


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

1617839091721.png


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

Zot

Well-known Member
Joined
Nov 26, 2020
Messages
1,170
Office Version
  1. 2016
Platform
  1. Windows
I wonder why for 3rd example the Reel Size is Large.
 

Zot

Well-known Member
Joined
Nov 26, 2020
Messages
1,170
Office Version
  1. 2016
Platform
  1. Windows
The No of Reel seems to follow the Qty in column B, right?
 

Watch MrExcel Video

Forum statistics

Threads
1,129,795
Messages
5,638,375
Members
417,024
Latest member
Mrpica01

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top