Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
dependent lists on data validation | Excel Discussion (Misc queries) | |||
Data Validation - Dependent Lists & Conditional Formatting | Excel Worksheet Functions | |||
Data Validation and Dependent Lists Q | Excel Worksheet Functions | |||
Data Validation & Dependent Lists | Excel Worksheet Functions | |||
Data Validation - Create dependent lists | Excel Discussion (Misc queries) |