Words with a value

[cathrinebrat]

Hi,

Hi,

I'm going to create a list of participants for a party. Everyone can choose from different dishes on the menu, and they have different prices. Therefore, I would like to have a formula that allows the price to be automatically added to a total when a dish is selected. The simplest way would be to have a hidden value for each dish. For example, "Christmas Plate" costs 419 NOK, "Pinnekjøtt" costs 495 NOK, and so on. In addition, everyone should choose a dessert, but it comes with a fixed price of 95 NOK.

Is there a way to either assign values to cells without them being visible or to specify that a specific word corresponds to a sum?

[#2]

Hi @cathrinebrat,

you can follow these steps:

1. Create a new Excel workbook and save it.
   
   
2. In the first worksheet, create a table with the following columns (for example):
   • Participant Name
   • Selected Dish
   • Price
     
     
3. In the "Selected Dish" column, create a dropdown list using Data Validation. To do this, select the "Selected Dish" column and then click on the Data tab.
   In the Data Tools group, click on Data Validation.
   In the Data Validation dialog box, select the List tab and then enter the range of cells that contain the valid dish selections.
   For example, if your dish selections are in cells B2:B10, then you would enter the range B2:B10 in the Source box. Click OK to close the Data Validation dialog box.
   
   
4. In the "Price" column, enter the prices for each dish.
   
   
5. In a cell below the table, use the following formula to calculate the total cost:

=SUMPRODUCT(SUMIF(B2:B10, UNIQUE(B2:B10), C2:C10))

This formula will sum the prices for each selected dish based on the unique selections in column B.

1. To hide the prices, format the "Price" column with the font color the same as the background color. This way, participants won't see the prices, but the calculations will still work.
   
   
2. To protect the worksheet and lock the cells containing the prices, click on the Review tab and then click on Protect Sheet. In the Protect Sheet dialog box, enter a password and then select the "Protect ranges" checkbox. In the Protect Ranges dialog box, select the "Price" column and then click OK. Click OK again to close the Protect Sheet dialog box.

Now, you can share the Party Cost Calculator workbook with the participants. They can select their desired dishes in the "Selected Dish" column and the total cost will be calculated automatically.

Please click Mark as Best Response & Like if my post helped you to solve your issue.
This will help others to find the correct solution easily. It also closes the item.

If the post was useful in other ways, please consider giving it Like.

Kindest regards,

Leon Pavesic
(LinkedIn)