How do I make an Excel Field auto populate based upon selection of previous field?

I've attached an example file for you to check how this works. On column B you select one option and the options on column C are changed based on that selection.

jppinto
Data-Validation-Example.xls

Thank you very much Jppinto,

I'm making some progress but I can't make step CVL8 from your link work.  Everytime i click ok i receive an error that "a named range cannot be found."  I thought "Markets" was my Named Range but it doesn't seemt to find it.  I'm very in-experienced with excel so I'm hoping I'm making a simple mistake.

Ellsworth
up-to-CVL8.xlsx

The formula for the second Data Validation list should be:

=CHOOSE(VLOOKUP(O2,Sheet2!$A$3:$B$6,2,FALSE),EnergyMinerals,BaseMetals,IndustrialMinerals,PreciousMetals)

I've attached a working sample.

jppinto
up-to-CVL8.xlsx

Jppinto,

I noticed that there is no database sheet for the example you gave me.  I think I could figure it out if ihad that.

Thanks,

Ellsworth

Yes there is! CVL2 is the image of the "database" sheet!

Actually Jppinto,

if you have the original Country and City excel document I think I could figure things out if I could look at it.

Thanks,

ellsworth

I don't have the original file here at work. But I attached your sample file working with the correct formula...did you saw it? Do you still need help putting this to work on your file?

Sorry Jppinto I didn't see the two replies you made I'll try that formula

So that formula gives me a different error:

"You cannot use references to other workstheets or workbooks for Data Validation criteria"

Okay so I'm a moron when it comes to excel but as far as i can tell the sample file doesn't bring up the data pertaining to the previous field selection.  I don't even see a Data Validation formula in the P2 cell.

Sorry to be a pain Jppinto and thank you for your patience

For example if I select Base Metals in Cell O2 from the sample, then I'd like only "Aluminum", "Copper/Nickel/Zinc/Lead", "Iron/Titanium" and "Others" to be the only options in the drop down menu in Cell P2

So i just did everything that was listed on the (https://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/A_4454-Cascading-Validation-Lists.html) link, and I still get the "You cannot use references to other workstheets or workbooks for Data Validation criteria" error again.  I'm messing something up from the get go.  

The indexes are colored red on the database sheet; are there any specific instructions for creating indexes or just number everything in red and that is all I need for Indexes?

I'm attaching the file again...I checked, did the example you posted, and it only gives me the options on cell P2 you mentioned when I select "Base Metals" on cell O2!

Don't see what is the problem, it's working with me! Are you sure you are doing it right?
up-to-CVL8.xlsx

The red font is just to indicate that the number on column B on CVL2 are related to the number on row 1 oc CVL1. In other words, country "England" corresponds to the index 1 wich will point to the range name "England" to pick the cities from that range.

Did you check the file I attached? Is it working fine for you? For me it's OK.

And now I just tried the instructions on MS Excel 2010 at the website: https://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/A_4454-Cascading-Validation-Lists.html, and now i receive the error: "The Source currently evaluates to an error. Do you want to continue?"

There is something very basic that I'm missing in this.....I think

Just say "Yes" to that question. It works anyway.

No that file didn't work

I clicked yes and it didn't work.  When I choose England and then try to choose a city, no cities are shown when click the drop down arrow

Do you want to post YOUR file for me to take a look?

yea that would be great Test-with-countries.xlsx

Test-with-countries.xlsx

Are you on Excel 2010?  I'm on 2007.  When I opened this excel file France was already in the Country Cell(CellB1) and Touluse was in the City Cell(Cell B2) and the cell is Yellow.  

So I tried changing France to England, which worked, and Touluse stayed the same.  Also there isn't a drop down box for Touluse(City Cell B2) nor is the Validation formula in Data Validation: =CHOOSE(VLOOKUP(B1,Sheet3!$A$3:$B$5,2,FALSE),England,France,Portugal)

How did you make the cell yellow?

NM i figured out how to make the cell yellow

If you change France to England, and go to cell B2, there is a drop down there and if you open it will only show cities from England now, while before changing the country, it only displayed cities from France.

So what I've found is that I couldn't make the formula: =CHOOSE(VLOOKUP(B1,Sheet3!$A$3:$B$5,2,FALSE),England,France,Portugal) work very well.

The formula that did work is Indirect(B1)  which was placed in cell B2 as Data validation criteria.  As far as I can tell that works but I just want to make sure I'm not limiting my self.  In your opinion Jppinto; doese INDIRECT(B1) make sense to you?

Now back to the oringinal Excel file i was working on:

I had to use this formula to make this work being that I had two-word selections:  =INDIRECT(SUBSTITUTE(O2," ",""))

also this website came in very handy: http://www.contextures.com/xldataval02.html

I'm not sure why the formula: =CHOOSE(VLOOKUP(B1,Sheet2!$A$3:$B$5,2,FALSE),England,France,Portugal) wouldn't work for me but from what I've researched it should have worked.  I'm probably making a simlple begginner mistake.  

Thank you for all your help Jppinto!!!!

Ellsworth