creating an order form: on an order form in column B there is a drop to pick a fabric then column D-G are quantity of each size you want to buy. Column H is total quantity I is unit cost. Need formula that calculates total cost based on the fabric chose in column B. Any advise
[Re-Titled by Moderator]
MacBook Air (M2, 2023)
I am not sure I am understanding your spreadsheet. Column B is a pop up menu to pick a fabric but I do not understand having four columns to specify the amount/size. Usually it is one column to specify the amount for a given item.
The formula will be most likely require a lookup table and the XLOOKUP function to get the cost(s) per unit for each amount, with the fabric types listed in column A and the cost per unit listed in column B (or, in your case, it might be four columns of costs). Tell us more about the four columns of "sizes" and how they relate to each other and the cost per unit. I don't know if it will require four lookups or one lookup and some math.
It is simple to make a lookup table so that you can take your fabric choice from column B and get the dollars per unit amount ($ per sq yd. or whatever) for that type of fabric and that can be multiplied by the amount (sq yds or whatever) that is being bought but you mentioned four cells (D,E,F, and G) that have "quantity of each size" and I have no idea what that means. Is one sq inches, the next sq ft, the next sq yds, and the next some even larger unit of measure? Do each of these columns have different pricing per unit or is it just simple math (i.e., 1 sq ft is the same price as 144 sq inches, 1 sq yd is the same price as 9 sq ft, etc.).
Do you have a screenshot of the table you are creating? Can you post it here?
I'm not trying to be difficult but the order form you are describing is not complete.
Column B is the fabric.
Columns D-G will provide the cost for each size, which will be filled in using formulas that look up the prices from a table where you have all the prices for everything.
But where/how does someone enter the quantity of each (or any) size they wish to buy? It cannot be done in D-G because they have formulas in them.
Consider this instead:
Column B is the fabric from a popup menu
Column C is the size from another pop up menu (S, M, L, XL)
Column D is the price for one shirt of that fabric type and size, which is filled in using a formula that looks up the price from a table that has all the prices.
Column E is the quantity desired
Column F is the total cost for this row = column D x column E
columns D-G are sizes. so if you like a curtain tshirt and want to buy in few different sizes. Supima is $34 100% cotton is $22 Tri-blend is $26. So when you say supima the $34 dollars will pop up etc
Or, if the price is the same for all sizes, column C could be for the price/each, columns D-G are the quantities of each size (i.e., you can order multiple different sizes on one row of the table) and column H does the math to get the total for the row?
Column B has drop down in cell. You can choose tri-blend, cotton or supima. i want column I ( unit price) to show $ amount for the fabric choice.
I need a formula for creating an order form in Numbers
