Help with Excel Auto Fill

Facts: Customer orders 15 Widgets.
Material is XYZ 71000
Thickness required is 6mm
Length of Widget 160mm
Breadth of Widget is 120mm

Calculation 1: Length Breadth Total SqM per Widget Number of Widgets Total SqM
I work out Square Metres of Total of Widgets (Order) 350 280 0.098 15 1.47

Calculation 2:
I only sell FULL or HALF or QUARTER or ONE EIGHTH Sheets Cost Price psm XYZ mm
Sheet Sizes of Material XYZ 71000 mm mm Sheet SqM Cost Price of 6mm Material SHEET R305 71000 3
FULL 3050 2050 6.2525 R3645.21 R391 71000 4
HALF 2050 1525 3.12625 R1822.60 R520 71000 5
QUARTER 1525 1025 1.563125 R911.30 R583 71000 6
EIGHTH 1025 765 0.784125 R457.14 R777 71000 8
R972 71000 10
R1232 71000 12
In the above example I can only fit the TOTAL SqM required into a QUARTER SHEET 1.563125 SqM and I'll have 0.093125 SqM Left as an offcut R1524 71000 15
R2083 71000 18
Question: R2483 71000 20
What is the Cost Price of a Quarter Sheet of 6mm Material XYZ 71000 R911.30 R3104 71000 25

AUTOMATED CALCULATION REQUIRED
I need to automate the Cost of Material required for my Sales Staff.
Basically they should have a TABLE that they populate with Widget L & B and Material Type and Material Thickness. This multiplied by Widget SqM and Material Cost should deliver the COST PRICE OF THE WIDGET RAW MATERIAL.

I'm thinking I should have ….
1. All my different Material NAMES listed in a Drop Down List
2. The variousTHICKNESSES listed in another Drop Down List
3. When I click on Material NAME in Drop Down 1 AND then Material THICKNESS in Drop Down 2
4. This should activate a Formula to give me the FULL SHEET price of the relevant Material & Thickness

A. An empty field for Length of Widget
B. An empty field for Width of Widget
C. Filling in A and B should deliver SqM of ONE Widget
D. An empty field for Number of Widgets required
E. An automated answer of Total SqM required
G. An automated answer that E above will only fit into FULL or HALF or QUARTER or EIGTH
H. An automated answer of the Cost Price of G above.

i) A mistake in a Sales Person's calculations could lead to heavy Losses which is why I would prefer a series of Drop Downs and Formulae.

Hope someone can help. (Hope I haven't forgotten something!)

Best,
Adum

Hi,

Please see file attached.

Look forward to your most excellent help.

Regards,
Adum