Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Validation lists
Hello all,
I need some help on using functions with a validation list. There are three columns Catagory, Product and Pack size. How I would like it to work is 1st select a catagory from a validation list, then in the product column validation list will give a list of all the products in that catagory, then in the pack size column the pack size list will appear in the validation list. This works well until I want to clear the list. If I don't work backward by selecting the pack size and choosing blank then the product then the catagory. I have used the following formulas to try and get the lists in the corresponding row to show blank in the cell when I select the blank in the Catagory validation list. Product =IF(ISBLANK(A$2),"",CHOOSE(MATCH($A2,Catagories,0) ,Spirits,Liqueurs,Aperitiefs___Digestifs,Wines____ Champ,Boxed_Wines,Bottle_Beers,D_Beers,Minerals,Su ndries,"")) Pack size =IF(ISBLANK($A2),"",IF(VLOOKUP($A2,Cata01,2,FALSE) ="Volume",Volume,IF(VLOOKUP($A2,Cata01,2,FALSE)="M ass",Mass,""))) Minerals, volumes etc are range names. If you can help please do so, and if you can suggest a better function for the existing formulas and or method please do so. Thank you and best regards Max |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Validation lists
Hi,
Here are the steps for creating a dynamic data validation list: 1. Create three or more list in the spreadsheet. For example: A B C New York Quebec Canada Seattle Toronto US Miami Victoria You would name lists: US (A1:A3), Canada (B1:B3), Countries (C1:C2) this last is optional 2. Select the cell where you want the initial validation list, for this example, D1 3. Choose Data, Validation 4. From the Allow drop-down and choose List 5. In the Source box enter the following formula =Countries 6. Move to the location where you want the dynamic (changing) list to appear, say for example E1 7. Choose Data, Validation, List and in the Source box enter the formula =INDIRECT(D1) You can also make a self-referential dynamic data validation list. If this helps, please click the Yes button. Cheers, Shane Devenshire "Max" wrote: Hello all, I need some help on using functions with a validation list. There are three columns Catagory, Product and Pack size. How I would like it to work is 1st select a catagory from a validation list, then in the product column validation list will give a list of all the products in that catagory, then in the pack size column the pack size list will appear in the validation list. This works well until I want to clear the list. If I don't work backward by selecting the pack size and choosing blank then the product then the catagory. I have used the following formulas to try and get the lists in the corresponding row to show blank in the cell when I select the blank in the Catagory validation list. Product =IF(ISBLANK(A$2),"",CHOOSE(MATCH($A2,Catagories,0) ,Spirits,Liqueurs,Aperitiefs___Digestifs,Wines____ Champ,Boxed_Wines,Bottle_Beers,D_Beers,Minerals,Su ndries,"")) Pack size =IF(ISBLANK($A2),"",IF(VLOOKUP($A2,Cata01,2,FALSE) ="Volume",Volume,IF(VLOOKUP($A2,Cata01,2,FALSE)="M ass",Mass,""))) Minerals, volumes etc are range names. If you can help please do so, and if you can suggest a better function for the existing formulas and or method please do so. Thank you and best regards Max |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Validation lists
Hello Shane,
Your method works, but I need to add another list that is linked. As in my example Catagory say Beer, Wine, Snacks, Spirits. (in cell A2) For each Catagory list a list of Products are available in cell B2. This in turn will allow a third cell say C1 to display in a validation list the Pack sizes for these products. If you select beer in cell A1. In B1 you validation list will give a selection of various beers. In Cell C1 you want the validation list for a Pack size, eg 33cl, or 50cl or for the thirsty 1lt. For Pack size there would be measures of Volume and Weight or Mass. How do I get to linking to the third list linked to the Pack size? Thank you Max "Shane Devenshire" wrote: Hi, Here are the steps for creating a dynamic data validation list: 1. Create three or more list in the spreadsheet. For example: A B C New York Quebec Canada Seattle Toronto US Miami Victoria You would name lists: US (A1:A3), Canada (B1:B3), Countries (C1:C2) this last is optional 2. Select the cell where you want the initial validation list, for this example, D1 3. Choose Data, Validation 4. From the Allow drop-down and choose List 5. In the Source box enter the following formula =Countries 6. Move to the location where you want the dynamic (changing) list to appear, say for example E1 7. Choose Data, Validation, List and in the Source box enter the formula =INDIRECT(D1) You can also make a self-referential dynamic data validation list. If this helps, please click the Yes button. Cheers, Shane Devenshire "Max" wrote: Hello all, I need some help on using functions with a validation list. There are three columns Catagory, Product and Pack size. How I would like it to work is 1st select a catagory from a validation list, then in the product column validation list will give a list of all the products in that catagory, then in the pack size column the pack size list will appear in the validation list. This works well until I want to clear the list. If I don't work backward by selecting the pack size and choosing blank then the product then the catagory. I have used the following formulas to try and get the lists in the corresponding row to show blank in the cell when I select the blank in the Catagory validation list. Product =IF(ISBLANK(A$2),"",CHOOSE(MATCH($A2,Catagories,0) ,Spirits,Liqueurs,Aperitiefs___Digestifs,Wines____ Champ,Boxed_Wines,Bottle_Beers,D_Beers,Minerals,Su ndries,"")) Pack size =IF(ISBLANK($A2),"",IF(VLOOKUP($A2,Cata01,2,FALSE) ="Volume",Volume,IF(VLOOKUP($A2,Cata01,2,FALSE)="M ass",Mass,""))) Minerals, volumes etc are range names. If you can help please do so, and if you can suggest a better function for the existing formulas and or method please do so. Thank you and best regards Max |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Validation lists
Hello again Shane,
You refered to making a self-referential dynamic data validation list. Would you please expalin how I can do this? Thank you and best regards Max "Max" wrote: Hello Shane, Your method works, but I need to add another list that is linked. As in my example Catagory say Beer, Wine, Snacks, Spirits. (in cell A2) For each Catagory list a list of Products are available in cell B2. This in turn will allow a third cell say C1 to display in a validation list the Pack sizes for these products. If you select beer in cell A1. In B1 you validation list will give a selection of various beers. In Cell C1 you want the validation list for a Pack size, eg 33cl, or 50cl or for the thirsty 1lt. For Pack size there would be measures of Volume and Weight or Mass. How do I get to linking to the third list linked to the Pack size? Thank you Max "Shane Devenshire" wrote: Hi, Here are the steps for creating a dynamic data validation list: 1. Create three or more list in the spreadsheet. For example: A B C New York Quebec Canada Seattle Toronto US Miami Victoria You would name lists: US (A1:A3), Canada (B1:B3), Countries (C1:C2) this last is optional 2. Select the cell where you want the initial validation list, for this example, D1 3. Choose Data, Validation 4. From the Allow drop-down and choose List 5. In the Source box enter the following formula =Countries 6. Move to the location where you want the dynamic (changing) list to appear, say for example E1 7. Choose Data, Validation, List and in the Source box enter the formula =INDIRECT(D1) You can also make a self-referential dynamic data validation list. If this helps, please click the Yes button. Cheers, Shane Devenshire "Max" wrote: Hello all, I need some help on using functions with a validation list. There are three columns Catagory, Product and Pack size. How I would like it to work is 1st select a catagory from a validation list, then in the product column validation list will give a list of all the products in that catagory, then in the pack size column the pack size list will appear in the validation list. This works well until I want to clear the list. If I don't work backward by selecting the pack size and choosing blank then the product then the catagory. I have used the following formulas to try and get the lists in the corresponding row to show blank in the cell when I select the blank in the Catagory validation list. Product =IF(ISBLANK(A$2),"",CHOOSE(MATCH($A2,Catagories,0) ,Spirits,Liqueurs,Aperitiefs___Digestifs,Wines____ Champ,Boxed_Wines,Bottle_Beers,D_Beers,Minerals,Su ndries,"")) Pack size =IF(ISBLANK($A2),"",IF(VLOOKUP($A2,Cata01,2,FALSE) ="Volume",Volume,IF(VLOOKUP($A2,Cata01,2,FALSE)="M ass",Mass,""))) Minerals, volumes etc are range names. If you can help please do so, and if you can suggest a better function for the existing formulas and or method please do so. Thank you and best regards Max |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Validation lists
Hi,
You can just extend the idea used for the first dynamic validation list to the second list, its exactly the same technique. Cheers, Shane Devenshire "Max" wrote: Hello Shane, Your method works, but I need to add another list that is linked. As in my example Catagory say Beer, Wine, Snacks, Spirits. (in cell A2) For each Catagory list a list of Products are available in cell B2. This in turn will allow a third cell say C1 to display in a validation list the Pack sizes for these products. If you select beer in cell A1. In B1 you validation list will give a selection of various beers. In Cell C1 you want the validation list for a Pack size, eg 33cl, or 50cl or for the thirsty 1lt. For Pack size there would be measures of Volume and Weight or Mass. How do I get to linking to the third list linked to the Pack size? Thank you Max "Shane Devenshire" wrote: Hi, Here are the steps for creating a dynamic data validation list: 1. Create three or more list in the spreadsheet. For example: A B C New York Quebec Canada Seattle Toronto US Miami Victoria You would name lists: US (A1:A3), Canada (B1:B3), Countries (C1:C2) this last is optional 2. Select the cell where you want the initial validation list, for this example, D1 3. Choose Data, Validation 4. From the Allow drop-down and choose List 5. In the Source box enter the following formula =Countries 6. Move to the location where you want the dynamic (changing) list to appear, say for example E1 7. Choose Data, Validation, List and in the Source box enter the formula =INDIRECT(D1) You can also make a self-referential dynamic data validation list. If this helps, please click the Yes button. Cheers, Shane Devenshire "Max" wrote: Hello all, I need some help on using functions with a validation list. There are three columns Catagory, Product and Pack size. How I would like it to work is 1st select a catagory from a validation list, then in the product column validation list will give a list of all the products in that catagory, then in the pack size column the pack size list will appear in the validation list. This works well until I want to clear the list. If I don't work backward by selecting the pack size and choosing blank then the product then the catagory. I have used the following formulas to try and get the lists in the corresponding row to show blank in the cell when I select the blank in the Catagory validation list. Product =IF(ISBLANK(A$2),"",CHOOSE(MATCH($A2,Catagories,0) ,Spirits,Liqueurs,Aperitiefs___Digestifs,Wines____ Champ,Boxed_Wines,Bottle_Beers,D_Beers,Minerals,Su ndries,"")) Pack size =IF(ISBLANK($A2),"",IF(VLOOKUP($A2,Cata01,2,FALSE) ="Volume",Volume,IF(VLOOKUP($A2,Cata01,2,FALSE)="M ass",Mass,""))) Minerals, volumes etc are range names. If you can help please do so, and if you can suggest a better function for the existing formulas and or method please do so. Thank you and best regards Max |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Validation lists
Hi,
Let me explain this with an example: Suppose you have a list of countries and when you pick an item from that list the list will change to show the states in that country (in the same list). And you can redisplay the coutry list from a state list, again not needing to move from one list to another. 1. So here are four lists in columns K,L, M & N US Countries Countries Countries Canada Alabama British Columbia Mexico Alaska Quebec Costa Rica Arizona Ontario Arkansas California 2. You apply range names to each as follows: Countries, US, Canada, Alabama respectively. Notice the last list has only one element, the name of the list from which it was picked. Here I have named that one cell Alabama. But since you can give a cell as many names as you want you can name it in such a way that it is the end cell for all state lists. 3. In cell A1 type the word Countries 4. In cell A1 create a data validation with the List option and the formula =INDIRECT(A1) and click OK. Now when you open the data validation drop down in A1 you will see the list of countries, pick US. Now open the data validation drop down and you will see a list of US states with Countries at the top. If you pick Countries you will now find the countries list in the data validation drop down - you have returned to the original list. Don't pick any state except Alabama, because at present we haven't created any named ranges for those other states, so you will be trapped. If all you want the user to do is pick a state then you could create named ranges for each state which had Countries as its only element. If this helps, please click the Yes button cheers, Shane Devenshire "Max" wrote: Hello again Shane, You refered to making a self-referential dynamic data validation list. Would you please expalin how I can do this? Thank you and best regards Max "Max" wrote: Hello Shane, Your method works, but I need to add another list that is linked. As in my example Catagory say Beer, Wine, Snacks, Spirits. (in cell A2) For each Catagory list a list of Products are available in cell B2. This in turn will allow a third cell say C1 to display in a validation list the Pack sizes for these products. If you select beer in cell A1. In B1 you validation list will give a selection of various beers. In Cell C1 you want the validation list for a Pack size, eg 33cl, or 50cl or for the thirsty 1lt. For Pack size there would be measures of Volume and Weight or Mass. How do I get to linking to the third list linked to the Pack size? Thank you Max "Shane Devenshire" wrote: Hi, Here are the steps for creating a dynamic data validation list: 1. Create three or more list in the spreadsheet. For example: A B C New York Quebec Canada Seattle Toronto US Miami Victoria You would name lists: US (A1:A3), Canada (B1:B3), Countries (C1:C2) this last is optional 2. Select the cell where you want the initial validation list, for this example, D1 3. Choose Data, Validation 4. From the Allow drop-down and choose List 5. In the Source box enter the following formula =Countries 6. Move to the location where you want the dynamic (changing) list to appear, say for example E1 7. Choose Data, Validation, List and in the Source box enter the formula =INDIRECT(D1) You can also make a self-referential dynamic data validation list. If this helps, please click the Yes button. Cheers, Shane Devenshire "Max" wrote: Hello all, I need some help on using functions with a validation list. There are three columns Catagory, Product and Pack size. How I would like it to work is 1st select a catagory from a validation list, then in the product column validation list will give a list of all the products in that catagory, then in the pack size column the pack size list will appear in the validation list. This works well until I want to clear the list. If I don't work backward by selecting the pack size and choosing blank then the product then the catagory. I have used the following formulas to try and get the lists in the corresponding row to show blank in the cell when I select the blank in the Catagory validation list. Product =IF(ISBLANK(A$2),"",CHOOSE(MATCH($A2,Catagories,0) ,Spirits,Liqueurs,Aperitiefs___Digestifs,Wines____ Champ,Boxed_Wines,Bottle_Beers,D_Beers,Minerals,Su ndries,"")) Pack size =IF(ISBLANK($A2),"",IF(VLOOKUP($A2,Cata01,2,FALSE) ="Volume",Volume,IF(VLOOKUP($A2,Cata01,2,FALSE)="M ass",Mass,""))) Minerals, volumes etc are range names. If you can help please do so, and if you can suggest a better function for the existing formulas and or method please do so. Thank you and best regards Max |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Validation lists
Hello Shane,
Thank you very much, your idea has saved me some time. Thank you for your help and patients. Max "Shane Devenshire" wrote: Hi, You can just extend the idea used for the first dynamic validation list to the second list, its exactly the same technique. Cheers, Shane Devenshire "Max" wrote: Hello Shane, Your method works, but I need to add another list that is linked. As in my example Catagory say Beer, Wine, Snacks, Spirits. (in cell A2) For each Catagory list a list of Products are available in cell B2. This in turn will allow a third cell say C1 to display in a validation list the Pack sizes for these products. If you select beer in cell A1. In B1 you validation list will give a selection of various beers. In Cell C1 you want the validation list for a Pack size, eg 33cl, or 50cl or for the thirsty 1lt. For Pack size there would be measures of Volume and Weight or Mass. How do I get to linking to the third list linked to the Pack size? Thank you Max "Shane Devenshire" wrote: Hi, Here are the steps for creating a dynamic data validation list: 1. Create three or more list in the spreadsheet. For example: A B C New York Quebec Canada Seattle Toronto US Miami Victoria You would name lists: US (A1:A3), Canada (B1:B3), Countries (C1:C2) this last is optional 2. Select the cell where you want the initial validation list, for this example, D1 3. Choose Data, Validation 4. From the Allow drop-down and choose List 5. In the Source box enter the following formula =Countries 6. Move to the location where you want the dynamic (changing) list to appear, say for example E1 7. Choose Data, Validation, List and in the Source box enter the formula =INDIRECT(D1) You can also make a self-referential dynamic data validation list. If this helps, please click the Yes button. Cheers, Shane Devenshire "Max" wrote: Hello all, I need some help on using functions with a validation list. There are three columns Catagory, Product and Pack size. How I would like it to work is 1st select a catagory from a validation list, then in the product column validation list will give a list of all the products in that catagory, then in the pack size column the pack size list will appear in the validation list. This works well until I want to clear the list. If I don't work backward by selecting the pack size and choosing blank then the product then the catagory. I have used the following formulas to try and get the lists in the corresponding row to show blank in the cell when I select the blank in the Catagory validation list. Product =IF(ISBLANK(A$2),"",CHOOSE(MATCH($A2,Catagories,0) ,Spirits,Liqueurs,Aperitiefs___Digestifs,Wines____ Champ,Boxed_Wines,Bottle_Beers,D_Beers,Minerals,Su ndries,"")) Pack size =IF(ISBLANK($A2),"",IF(VLOOKUP($A2,Cata01,2,FALSE) ="Volume",Volume,IF(VLOOKUP($A2,Cata01,2,FALSE)="M ass",Mass,""))) Minerals, volumes etc are range names. If you can help please do so, and if you can suggest a better function for the existing formulas and or method please do so. Thank you and best regards Max |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to clear validation lists based on other validation lists | Excel Discussion (Misc queries) | |||
Data validation with validation lists and combo boxs | Excel Discussion (Misc queries) | |||
Validation lists | Excel Discussion (Misc queries) | |||
Using Lists/Validation | Excel Discussion (Misc queries) | |||
lists and validation | Excel Worksheet Functions |