Quantity x drop down list with calc = total

[noobspreader]

Hi Libre community,

I am new to using calc, I was just wondering if someone can help me with a formula to use or refer me to a link that is suitable for a newbie to understand.

Problem:
I would like a quantity column that I can input a numeral value; and on the next column have a drop down list with about 20 product names (ie. eggs, bread, sugar, etch), each of the product names have a numerical price value, then on the third column give me a total value of whatever the product name is multiplied by the numerical value from the first column.

Because the products are reoccurring, it has to be selected from a drop down list rather than typed each time.

Example:
eggs = $4.00
bread = $7.50
sugar = $3.00

Column A                       Column B                             Column C
(Numerical value)     (drop down list of products)                (total value)
3                                 eggs                              $12.00
1                                 bread                             $7.50
5                                 sugar                             $15.00

I would appreciate the help.

[anon73440385]

Hello,

please see the following attached file, which contains 2 sheets:
Product-Pricer.ods

Sheet: Calculator - contains the dropdown in column “Product” abn performs the calculation of “Total” based on the quantity given in column quantity.

Sheet: Prodcuct&Price - contains the base data required for

• Product names (used in the dropdown)
• Price per unit (as in your question)

Dropdown
This is implemented by defining data allowed to be entered in column “Product” - the list of valid data is taken from column A of sheet Product&Price. To see how that works, do the following:

• Select column B of sheet Calculator
• Go to menu Data -> Validity and see the settings, which has the following settings:
  Allow: Dropdown Cell Range
  Source: $'Product&Price'.$A$2:$A$3

More info on defining Vailidity - see LibreOffice Help - Validity of Cell Contents

Formula to calculate Total

The formula =IF(AND(A2<>"";B2<>"");VLOOKUP(B2;$'Product&Price'.$A$2:$B$4;2;0)*A2;"-") performes the following:

• Checks, whether Quantity and Product are set
• Evaluates the unit price from sheet Product&Price for the selected product
• Multiplies product unit price found with quantity given

More info on function IF - see Libre Office Help - IF
More info on function AND - see Libre Office Help - AND
More info on function VLOOKUP - see Libre Office Help - VLOOKUP

Hope that helps.

If the answer is correct or helped you to solve your problem, please click the check mark () next to the answer.