Assign values to dropdown menu selections

Hello,


I have a dropdown menu in cell A9 with 5 selections in it. I want 4 of the selections to have cells automatically populate when they are selected (I have embedded IF statements into the cells that I want to change). However, I want to have one of the selections in the dropdown menu be an "open" selection, where I can enter in my own values in the same cells that would be populated if one of the other 4 selections were picked.

Does anybody know how I can tell the IF statement to allow me to input my own data when the "open" selection is chosen, while still re-populating the fields when the other 4 selections are picked?

Unfortunately, I can't upload an example because there is private information that I can't share. If you want me to clarify anything, please let me know!

Thanks!

This needs quite a bit of clarification:
Is the source of your dropdown a form control, an active X control, or data validation?
Do you want formulas in your target cells to evaluate your "dropdown" selection or will you be updating hard coded values based on dropdown selection?

Can you provide a mockup of what you'd like to occur in an example spreadsheet?

OK - you've got your formula pulling values from various cells on sheet 3 based on what's in cell A9. Does A9 contain the validation? I'm assuming it does, and that the choices are:
"Wellness","Healthy Back Non-Incentive","Healthy Back Incentive","Open"

If the user choses Open in cell A9, do you then want the ability to edit A9 and put in some other value?

Right now your formula will display values from sheet 3 based on selection but will not "populate" other cells.

Can you clarify? Also, a mockup would be very useful...

See attached workbook. Note that it doesn't rely on formulas at all, but on two VBA sub routines.

The first is the Worksheet_Change event attached to sheet 1. When the range A10 on that sheet changes in value, it then calls the FillTable sub routine. The FillTable routine relies on named ranges which correspond to the items in the drop down list. In order for that to work, I had to replace spaces and hyphens with the underscore character since those are forbidden characters in named ranges. Macros MUST be enabled for this to work.

Let me know if this does the trick for you.

Regards,
Tom