Data Validation and combination

[UTResearchAnalyst]

I am making an energy efficiency calculator for a fleet of vehicles. For
each year, make and model, I have specific information as to MPG, CO2, and
Air Pollution Score. I am trying to make a drop down list to choose year,
then based on year, makes that were purchased that year, then based on makes,
the models bought in that year of that make.

Each vehicle will have a row with columns: Year, Make, Model, MPG, Air
Quality. MPG and Air Quality be hard numbers, not selected in the drop-down.

Thank you for your help!

 

[Gord Dibben]

See Debra Dalgleish's site for Data Validation dependent lists.

http://www.contextures.on.ca/xlDataVal02.html


Gord Dibben MS Excel MVP

 

[UTResearchAnalyst]

Gord,

Thank you for your timely response. I have already been to this page and it
is great. However, I am still not able to get it working. Let me further
explain what is happening.

I have 1400 entries. Each organized into 5 columns. Year, Make, Model,
MPG, Air Quality.

I have been able to set it up so that when I select a Year, the list for
Make is updated. However, I am trying to filter this list. For example, we
have 15 models of Cheverolet and so the drop down menu has Cheverolet 15
times. I would like to filter this to just unique instances.

I hope this makes sense.

Thank you again for your help,

Brian

 

[Gord Dibben]

Don't use the Make column as your dependent list.

Create a list with just the manufacturers 'names.

i.e. Ford,GM,Nissan,etc.

Have another dependent list for Chev,Pontiac,Cadillac etc.

At some point you may have to use a VLOOKUP table to return values to adjacent
cells.

You CAN use the dependent list method to drill down, just takes a lot of work.

Have you tried any combinations of Autofilter to drill down?


Gord