Data validation dependent lists
 

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.

Data validation dependent lists
 

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 InsertNameDefine. 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 DataValidation.

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"
om wrote in message
...
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.

Data validation dependent lists
 

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?

"T. Valko" wrote:

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 InsertNameDefine. 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 DataValidation.

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"
om wrote in message
...
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.

Data validation dependent lists
 

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

"T. Valko" wrote:

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 InsertNameDefine. 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 DataValidation.

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"
om wrote in message
...
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.

Data validation dependent lists
 

You're welcome!

--
Biff
Microsoft Excel MVP


"Negative numbers in brackets"
om wrote in message
...
Biff, THanks......I've managed to open in Excel. Thanks once again.

"T. Valko" wrote:

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 InsertNameDefine. 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 DataValidation.

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"
om wrote in message
...
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.