[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

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

Posted on 2011-05-03
30
Medium Priority
?
362 Views
Last Modified: 2012-05-11
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
Comment
Question by:ellsworth2000
  • 19
  • 11
30 Comments
 
LVL 33

Accepted Solution

by:
jppinto earned 2000 total points
ID: 35514796
0
 
LVL 33

Expert Comment

by:jppinto
ID: 35514807
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
 

Author Comment

by:ellsworth2000
ID: 35515570
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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 33

Expert Comment

by:jppinto
ID: 35516158
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
 

Author Comment

by:ellsworth2000
ID: 35516190
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
 
LVL 33

Expert Comment

by:jppinto
ID: 35516198
Yes there is! CVL2 is the image of the "database" sheet!
0
 

Author Comment

by:ellsworth2000
ID: 35516420
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
 
LVL 33

Expert Comment

by:jppinto
ID: 35516440
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
 

Author Comment

by:ellsworth2000
ID: 35516451
Sorry Jppinto I didn't see the two replies you made I'll try that formula
0
 

Author Comment

by:ellsworth2000
ID: 35516552
So that formula gives me a different error:

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

0
 

Author Comment

by:ellsworth2000
ID: 35516576
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
 

Author Comment

by:ellsworth2000
ID: 35516609
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
 

Author Comment

by:ellsworth2000
ID: 35517046
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
 
LVL 33

Expert Comment

by:jppinto
ID: 35517047
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
 
LVL 33

Expert Comment

by:jppinto
ID: 35517068
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
 

Author Comment

by:ellsworth2000
ID: 35517162
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
 
LVL 33

Expert Comment

by:jppinto
ID: 35517167
Just say "Yes" to that question. It works anyway.
0
 

Author Comment

by:ellsworth2000
ID: 35517173
No that file didn't work
0
 

Author Comment

by:ellsworth2000
ID: 35517186
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
 
LVL 33

Expert Comment

by:jppinto
ID: 35517190
Do you want to post YOUR file for me to take a look?
0
 

Author Comment

by:ellsworth2000
ID: 35517244
yea that would be great Test-with-countries.xlsx
0
 
LVL 33

Expert Comment

by:jppinto
ID: 35517341
0
 

Author Comment

by:ellsworth2000
ID: 35517414
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
 

Author Comment

by:ellsworth2000
ID: 35517469
How did you make the cell yellow?
0
 

Author Comment

by:ellsworth2000
ID: 35517534
NM i figured out how to make the cell yellow
0
 
LVL 33

Expert Comment

by:jppinto
ID: 35517562
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
 

Author Comment

by:ellsworth2000
ID: 35517782
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
 

Author Comment

by:ellsworth2000
ID: 35517834
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
 

Author Comment

by:ellsworth2000
ID: 35517837
also this website came in very handy: http://www.contextures.com/xldataval02.html
0
 

Author Comment

by:ellsworth2000
ID: 35689774
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

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

After seeing numerous questions for Dynamic Data Validation I notice that most have used Visual Basic to solve the problem. This suggestion is purely formula based and can be used in multiple rows.
Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

834 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question