2 columns with dependent dropdown lists

Question

I'm trying to create two columns that populate cells based on their respective dropdown selectors. I'll break it into 2 parts.

Part 1: For example, in column B, i want rows 3-7 let's say to populate with fixed number values from a table on another sheet. That part i seem to have working using hlookup.

The part I'm struggling with is Part 2: I want column C to have a drop down to select various percentages (90%, 80% and so on) and then to calculate those percentages of the fixed values in column B (which will change depending on what is selected in column B's dropdown) and then populate rows 3-7 in column B with the percentage value selected. I think mostly i'm struggling to figure out how to create a dropdown that populates a formula (rather than a value) into a cell and to do that without breaking the formula.

Any help would be appreciated!

Answer 1

Dear Andrew Shumway  Thanks for using Microsoft products and posting in the community. Regarding your query, I would like to confirm that you are trying to override the original values of B3-B7 directly by multiplying the fixed values in B3-B7 with the result of the selected percentage after selecting the percentage in the drop-down menu of Column C? If so, this would actually create a circular reference problem, since the value in column B would be constantly changing based on the result of the calculations in column C. This is usually accomplished using VBA (Visual Basic for Applications).

Also, you mention “which will change depending on what is selected in column B's dropdown”, which seems to indicate that the values of B3-B7 are not fixed, but will change depending on what is selected in column B's dropdown. the selection in column B's dropdown. This is somewhat contradictory to your previous statement that column B is a fixed value. Could you please clarify further, are the values of B3-B7 fixed or do they change based on the selection of the drop down menu?

In order to better assist you, could you please let us know if you are using a personal or corporate account? This way we can provide more specialized official support according to your needs.

In addition, you are more than welcome to create a sample form showing the layout of the functionality you expect to implement. You can share this form with us and we will perform the necessary tests and configurations based on the content of your form to verify that it meets your specific needs. We look forward to hearing from you and will be happy to assist you.Best Regards,Maxine | Microsoft Community Support Specialist