Forum Discussion
Help with Excel
I am trying to create a spreadsheet with multiple columns that have drop down menus. I have the menus and columns created but I need to figure out how to assign values to each option in each drop down menu so that when I choose an option for each drop down menu in a row, it will spit out the final price of the chosen drop down items combined. I can provide screenshots and other clarifications if I explained this poorly. Thanks in advance!
CWertheimer To create drop-down menus in Excel, you can use the Data Validation feature and assign values to each option by creating a separate table. Here are the steps:
- Create a new sheet in your Excel workbook and make a table with the options in the first column and their corresponding values in the second column.
- Select the cells in the first column of the table, including the header, and give it a name using the Name Box in the top-left corner of the Excel window. For instance, you can name it "Options".
- Go back to the sheet where you want to create the drop-down menus. Choose the cell where you want the first drop-down menu to appear.
- Go to the Data tab and select Data Validation.
- In the Data Validation dialog box, choose "List" as the validation criteria and enter "=Options" (without the quotes) in the "Source" field.
- Click OK to close the Data Validation dialog box.
- Repeat the above steps for each column where you want a drop-down menu.
- To calculate the total based on the selected options, use a formula in the cell where you want to display the final price. For example, if the drop-down menus are in columns A, B, and C, and the corresponding values are in columns D and E in the table from step 1, you can use the following formula: =VLOOKUP(A1,Options,2,0)+VLOOKUP(B1,Options,2,0)+VLOOKUP(C1,Options,2,0)
This formula utilizes the VLOOKUP function to search the selected option in the table and return its corresponding value, and then adds up the values for all three columns.
Hope this helps!
Ayoub,
Thank you for the reply. Just wanted to clarify the issue you are helping me resolve. Essentially, I each cell in this picture attached has a drop down list with multiple selections per list. I want to be able to make each item in these drop down lists have a cost associated to them and then when all options are chosen in a row, it will spit out a final cost in the last column labeled "Total Price", as shown in picture. Will the solution that you just gave me be able to do this?
- It looks like the solution provided should work for your needs. It might be easier to implement the solution in Power BI instead of Excel.
