IF formula with limiting choices

In my worksheet I have a dropdown list in cell A1 that has 6 model homes names. In A2 I have 20 selections for lots to build on. Each model of home can only fit on certain lots. How to I limit my choice of lots if I choose a particular model of home. example: Urban Modern home can only be built on lots 1, 3, 12. When I choose Urban Modern from the drop down list I only want Lots 1, 3, 12 to be available to choose from.
Thank you in advance for your help.

You need a named range for each of the model homes, which lists the plots available. If you use the names of the model homes as the named range (with underscore instead of spaces), then your second DV drop-down would have this formula as its source:

=INDIRECT(SUBSTITUTE(A1," ","_"))

Attach a sample Excel workbook by following the instructions in the yellow banner at the top of the screen, and I can set this up for you.

Hope this helps.

Pete

Thank you Pete! On the attached worksheet the data is on the second worksheet "models". In column B there are the model choices and at the bottom are the lot choices.
So on the pricing spreadsheet in cell C9 is the dropdown box for models. If Country Meadow is selected I would like the the choices in C10 to be limited to
Lots; B1L65 (model worksheet B30), B1L66 (model worksheet B31), B1L69 (Model Worksheet B27), B1L70 (Model worksheet B28). If the model choice is Urban Modern then
the only lot choices should be B3L8, B3L9, B4L14, B4L15 (model worksheet cells B18, B23, B19, B20, respectively). I'm hoping if you start this that I will
be able to run with it.
Thank you again.

I've set this up for you in the attached file, using the data you have given me.

I've listed your models in column J and given those the named range "Models". To set up a named range you just merely have to select the heading and the values below it then click on Formulas | Create from Selection (in the Named Ranges section) and click on Top Row then OK. I've also listed the plots to the right of the models and given them a named range in the same way, although clicking Left column rather than Top Row.

The named ranges are needed for the data validation drop-downs on the other sheet, although you should be aware that spaces are not allowed in a name, and so Excel automatically changes them to an underscore. You can see this by clicking Name Manager.

In the first sheet I've set up a data validation drop-down in cell C9, using the named range Models. I've also set up a dependent data validation drop-down in C10, although this uses a formula similar to the one I have given you to define the list, i.e.:

=INDIRECT(SUBSTITUTE($C$9," ","_"))

You can see that this takes the model in C9 and changes the spaces to underscores, thus giving the appropriate lot choices for that model.

So, all you have to do is add the lot numbers to the appropriate models in the Models sheet and set up named ranges for each of them, and then you're done.

Hope this helps.

Pete

Thank you Pete!! You are a tremendous help. Your instructions are very helpful even though it will take me awhile to dissect the formulas.
This is exactly what I needed.

Pete. I attempted to duplicate what you did, but I have obviously missed a step. The models that I created don't allow for a dropdown box for C10 the way yours do. Please show me what I did wrong.

DV can be generated without name

Please Login or Register  to view this content.

Pete, I've attached the worksheet again with my attempt to add to the "models" and "lots" that you started. I'm a novice and have no idea what to do
to get the dropdown to work properly for the models I added ie. Garden Beauty.

It was because you had hyphens in those model types, and they also get turned into underscores (which my formula did not account for). I've removed the hyphens and extra spaces for you in the attached file, and reset the names for those ranges, and all seems to work as it should now.

Hope this helps.

Pete

Thanks Again Pete! This is fantastic!
Was there an easier way for me to set up F10? I used multiple "IF's", but as this housing development grows I can see how this could become difficult to manage.
I'm happy with it either way. Thank you.

Your monster formula in F10 can be simplified to this:

=IFERROR(VLOOKUP(C10,Models!$B$17:$C$31,2,0),"")

It does the same job, but is much more compact, and will return a blank if C10 is not found in the range - you could change this to something else if you like (e.g. zero instead of "" at the end).

If that takes care of your original question, please take a moment to select Thread Tools from the menu above and to the right of your first post in this thread, and mark the thread as SOLVED.

Also, since you are relatively new to the forum, you might like to know that you can directly thank those who have helped you by clicking on the small "star" icon located in the lower left corner of a post that you have found to be helpful (not just in this thread - for any post that has helped you). This also adds to the reputation of the poster (the small green bars in the poster's profile).

Hope this helps.

Pete