Data validation dependent lists

[Guest]

Using Excel 2003, I'm having trouble with dependent lists in data validation.
Here is the example:

There are 3 vehicle types (Prime Movers, Rigids and Other Equipment). On
selecting one of these, the next column shows up only those vehicle
configurations applying for each vehicle type. I've achieved this by use of
the INDIRECT function in the data valdiation set up. So far so good. (Level 1
dependent lists are working fine). The problem I have is when there is a
third list of data also dependent on the truck type eg the brand of the
vehicle type. There are different manufacturers for tankers compared with say
prime movers. I only want to see a list of the brands that apply for the
vehicle types selected in the first column of data.

I have already studied Contextures Data Validation Cheat Sheet which is
where I learnt to do the Level 1 dependent lists but not sure how to take it
to Level 2 and so on.

Any help most appreciated.

 

[T. Valko]

Here's a small sample file that demonstrates this. This file also contains
an event macro.

http://cjoint.com/?ljfvEPLrjA

There are 2 clients to choose from.
Each client has different job categories.
Each job category has different tasks.

The "trick" is building a hierarchical list using defined names to represent
each different range for a particular drop down.

To see these defined names goto Insert>Name>Define. The range names are also
highlighted in green on Sheet2.

To see the source for the drop downs select a particular drop down and then
goto Data>Validation.

The event macro is used to clear the selections when a different unrelated
selection is made. This ensures that only related selections can be made.

For example, I select client Joe then select JoeJob1. I change my mind and
select client Tom. Since JoeJob1 is unrelated to client Tom, JoeJob1 is
cleared from the 2nd dependent cell.

To view the macro code select Sheet1, right click the sheet tab and select
View Code.

--
Biff
Microsoft Excel MVP


"Negative numbers in brackets"

 

[Guest]

Thanks Biff, Only problem I have at this stage is getting the file into
excel. It opens in internet explorer and looks like excel but without all the
menus etc to be able to see the data validation approach you're
demonstrating. Any ideas?

 

[Guest]

Biff, THanks......I've managed to open in Excel. Thanks once again.

 

[T. Valko]

You're welcome!

--
Biff
Microsoft Excel MVP


"Negative numbers in brackets"