Skip to main content

Can anyone help with this?  I'm working with MS 365 and would like to add drop down lists for products that would then auto populate the price.  I went here and followed the instructions however it is not working http://office.microsoft.com/en...ist-HA102809802.aspx

If you like something I've posted please feel free to click the "like" button!

Original Post

Replies sorted oldest to newest

I used to be pretty good at Excel but don't have to use it as much as I used to. That being said, unless things have changed, I don't think that radio buttons or drop down menus can generate a value that can be used in a formula.

If the variables are few, then if/then might work. If the variables are great, then try VLOOKUP or HLOOKUP formulas.

Hey Art, here's how - In Excel add the choices you want in the dropdown to a column of cells.  Go to the Data tab and select Data Validation menu and data validation.  In the popup window select the setting tab and in the allow dropdown select list.  Enter the range of cells where you entered the choices earlier. You can enter additional text from the other tabs and then OK.  Voila' a dropdown in Excel.

Be careful if you reference the contents of the dropdown as it will result in the index value of the choice not the choice itself.  You need another formula for that...

 

So expanding on the original solution.  Add another column of data next to the choice column you used for the dropdown with the corresponding prices for each choice.  Next go to the formulas tab in the ribbon and select Name Manager -> New.  Enter "Data" in the range "Name" field and select the range of cells with all the choices and prices in them for the "Refers To" field and OK.

Finally in the cell next to the dropdown field enter this formula.
=vlookup(E5,Data,2,false)  E5 in this example would be the cell with the dropdown (yours maybe different).  You can then copy both columns down to replicate this in multiple rows.

Now when you select a choice from the dropdown, the price field updates automatically.  Happy computing!

Add Reply

Post
×
×
×
×
Link copied to your clipboard.
×
×