• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 368
  • Last Modified:

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

Hello Experts,

Please view the attached print screen of the Excel file I'm working with.  When one of the selections in cell O2 is made I'd like cell P2 to autopopulate with the corresponding data that is color coded.   Also this is being modified in Excel 2007 on WIndows XP sp3.

Many Thanks Experts,

Ellsworth prtscrn of excel doc prtscrn of excel doc SMALL jpeg format
0
ellsworth2000
Asked:
ellsworth2000
  • 19
  • 11
1 Solution
 
jppintoCommented:
0
 
jppintoCommented:
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
0
 
ellsworth2000Author Commented:
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
0
The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

 
jppintoCommented:
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
0
 
ellsworth2000Author Commented:
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
0
 
jppintoCommented:
Yes there is! CVL2 is the image of the "database" sheet!
0
 
ellsworth2000Author Commented:
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
0
 
jppintoCommented:
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?
0
 
ellsworth2000Author Commented:
Sorry Jppinto I didn't see the two replies you made I'll try that formula
0
 
ellsworth2000Author Commented:
So that formula gives me a different error:

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

0
 
ellsworth2000Author Commented:
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
0
 
ellsworth2000Author Commented:
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
0
 
ellsworth2000Author Commented:
So i just did everything that was listed on the (http://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?
0
 
jppintoCommented:
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
0
 
jppintoCommented:
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.
0
 
ellsworth2000Author Commented:
And now I just tried the instructions on MS Excel 2010 at the website: http://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
0
 
jppintoCommented:
Just say "Yes" to that question. It works anyway.
0
 
ellsworth2000Author Commented:
No that file didn't work
0
 
ellsworth2000Author Commented:
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
0
 
jppintoCommented:
Do you want to post YOUR file for me to take a look?
0
 
ellsworth2000Author Commented:
yea that would be great Test-with-countries.xlsx
0
 
jppintoCommented:
0
 
ellsworth2000Author Commented:
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)

0
 
ellsworth2000Author Commented:
How did you make the cell yellow?
0
 
ellsworth2000Author Commented:
NM i figured out how to make the cell yellow
0
 
jppintoCommented:
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.
0
 
ellsworth2000Author Commented:
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?
0
 
ellsworth2000Author Commented:
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," ",""))
0
 
ellsworth2000Author Commented:
also this website came in very handy: http://www.contextures.com/xldataval02.html
0
 
ellsworth2000Author Commented:
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
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Certified Penetration Testing

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

  • 19
  • 11
Tackle projects and never again get stuck behind a technical roadblock.
Join Now