Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Populate lists automatically
Hi,
I have a requirement which has 2 lists to be populated automatically. The 2nd list value is based on the 1st list. For eg: 1st List 2nd List Fruits (Should display list of all fruits mentioned) Vegetables (Should display list of all veg mentioned) Frozen Items (Should display list of all frozen items mentioned) If i select fruits, it should display only fruits. I have tried to other sites ( http://www.contextures.com/xlFunctions02.html) but there is a bug in it. It does not clear the previous values from the second list. Can someone help, with examples..Appreciated |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Populate lists automatically
Hi
The link you gave from Debra's site was the wrong one http://www.contextures.com/xlDataVal02.html If you follow Debra's instructions from here carefully, it will do just what you want. -- Regards Roger Govier "New2XL" wrote in message ... Hi, I have a requirement which has 2 lists to be populated automatically. The 2nd list value is based on the 1st list. For eg: 1st List 2nd List Fruits (Should display list of all fruits mentioned) Vegetables (Should display list of all veg mentioned) Frozen Items (Should display list of all frozen items mentioned) If i select fruits, it should display only fruits. I have tried to other sites ( http://www.contextures.com/xlFunctions02.html) but there is a bug in it. It does not clear the previous values from the second list. Can someone help, with examples..Appreciated |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Populate lists automatically
Hi,
I downloaded the sample file, and guess what? This formula also contains a bug. In the file, there are 2 columns, Category, and Item. Select category as Fruits, Select Apple from Item. (Works good). No select Vegetable from Category, and guess what, the item is still displaying apple. You have to click on the list on order to retrieve the vegetable list. My question is, can we clear that "Apple", when we select Vegetable?? Cheers "Roger Govier" wrote: Hi The link you gave from Debra's site was the wrong one http://www.contextures.com/xlDataVal02.html If you follow Debra's instructions from here carefully, it will do just what you want. -- Regards Roger Govier "New2XL" wrote in message ... Hi, I have a requirement which has 2 lists to be populated automatically. The 2nd list value is based on the 1st list. For eg: 1st List 2nd List Fruits (Should display list of all fruits mentioned) Vegetables (Should display list of all veg mentioned) Frozen Items (Should display list of all frozen items mentioned) If i select fruits, it should display only fruits. I have tried to other sites ( http://www.contextures.com/xlFunctions02.html) but there is a bug in it. It does not clear the previous values from the second list. Can someone help, with examples..Appreciated |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Populate lists automatically
Hi
This is not a bug. It is the way the system works. The only way to have it clear automatically, would be to use an Event in some VBA code to achieve a clearance of the cell. You would need to look at Debra's section on using Combo boxes http://www.contextures.com/xlDataVal10.html download the sample file http://www.contextures.com/DataValCombobox.zip and then make amendments to the code to clear the required cells first. For example, Name range J2:J8 as Weekday Name range L2:L8 as Month Enter Weekday in cell N2 and Month in cell N3 and Name range N2:N3 as Choice Change Data Validation on cells B2:B12 to be =Choice Change Data Validation on cells C2:C12 to be =INDIRECT(B2) Within the second of Debra's macros, after Set ws =Activesheet insert the following If Target.Column = 2 Then Target.Offset(0, 1) = "" End If Then, as you go to select Weekday or Month in any cell in the range B2:B12, the value in the adjacent cell in column C will go blank, before you choose the new value from the dropdown that is now appropriate to the option you have selected in column B -- Regards Roger Govier "New2XL" wrote in message ... Hi, I downloaded the sample file, and guess what? This formula also contains a bug. In the file, there are 2 columns, Category, and Item. Select category as Fruits, Select Apple from Item. (Works good). No select Vegetable from Category, and guess what, the item is still displaying apple. You have to click on the list on order to retrieve the vegetable list. My question is, can we clear that "Apple", when we select Vegetable?? Cheers "Roger Govier" wrote: Hi The link you gave from Debra's site was the wrong one http://www.contextures.com/xlDataVal02.html If you follow Debra's instructions from here carefully, it will do just what you want. -- Regards Roger Govier "New2XL" wrote in message ... Hi, I have a requirement which has 2 lists to be populated automatically. The 2nd list value is based on the 1st list. For eg: 1st List 2nd List Fruits (Should display list of all fruits mentioned) Vegetables (Should display list of all veg mentioned) Frozen Items (Should display list of all frozen items mentioned) If i select fruits, it should display only fruits. I have tried to other sites ( http://www.contextures.com/xlFunctions02.html) but there is a bug in it. It does not clear the previous values from the second list. Can someone help, with examples..Appreciated |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Populate lists automatically
Hi Roger,
Appreciate your help. However, the solution you are providing is making my life tougher. (;-). I am not a programmer, and the stated requirement is for users to fill up business forms. Now, we have to assume that users are dumb, and we just cannot leave any room for complications. Can you provide with a solution (even if it requires manual input). Its got to be simple, so that any person can maintain the file as well. (Operation perspective) Thank you once again. Cheers "Roger Govier" wrote: Hi This is not a bug. It is the way the system works. The only way to have it clear automatically, would be to use an Event in some VBA code to achieve a clearance of the cell. You would need to look at Debra's section on using Combo boxes http://www.contextures.com/xlDataVal10.html download the sample file http://www.contextures.com/DataValCombobox.zip and then make amendments to the code to clear the required cells first. For example, Name range J2:J8 as Weekday Name range L2:L8 as Month Enter Weekday in cell N2 and Month in cell N3 and Name range N2:N3 as Choice Change Data Validation on cells B2:B12 to be =Choice Change Data Validation on cells C2:C12 to be =INDIRECT(B2) Within the second of Debra's macros, after Set ws =Activesheet insert the following If Target.Column = 2 Then Target.Offset(0, 1) = "" End If Then, as you go to select Weekday or Month in any cell in the range B2:B12, the value in the adjacent cell in column C will go blank, before you choose the new value from the dropdown that is now appropriate to the option you have selected in column B -- Regards Roger Govier "New2XL" wrote in message ... Hi, I downloaded the sample file, and guess what? This formula also contains a bug. In the file, there are 2 columns, Category, and Item. Select category as Fruits, Select Apple from Item. (Works good). No select Vegetable from Category, and guess what, the item is still displaying apple. You have to click on the list on order to retrieve the vegetable list. My question is, can we clear that "Apple", when we select Vegetable?? Cheers "Roger Govier" wrote: Hi The link you gave from Debra's site was the wrong one http://www.contextures.com/xlDataVal02.html If you follow Debra's instructions from here carefully, it will do just what you want. -- Regards Roger Govier "New2XL" wrote in message ... Hi, I have a requirement which has 2 lists to be populated automatically. The 2nd list value is based on the 1st list. For eg: 1st List 2nd List Fruits (Should display list of all fruits mentioned) Vegetables (Should display list of all veg mentioned) Frozen Items (Should display list of all frozen items mentioned) If i select fruits, it should display only fruits. I have tried to other sites ( http://www.contextures.com/xlFunctions02.html) but there is a bug in it. It does not clear the previous values from the second list. Can someone help, with examples..Appreciated |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Populate lists automatically
Hi
If you are not going to use code, then you can't have the cell go blank if you make a change in the original source list. If the user wants to change an entry, go back to the cell and make the change in the source list, then in the dependent cell. You could use a formula in another cell to check the result in cell2 is valid and display a warning message in that 3rd cell. Suppose A1 contains Category and B1 contains Item In C1 enter =IF(ISERROR(VLOOKUP,B1,INDIRECT(A1),1,0)),"Wrong Entry","") -- Regards Roger Govier "New2XL" wrote in message ... Hi Roger, Appreciate your help. However, the solution you are providing is making my life tougher. (;-). I am not a programmer, and the stated requirement is for users to fill up business forms. Now, we have to assume that users are dumb, and we just cannot leave any room for complications. Can you provide with a solution (even if it requires manual input). Its got to be simple, so that any person can maintain the file as well. (Operation perspective) Thank you once again. Cheers "Roger Govier" wrote: Hi This is not a bug. It is the way the system works. The only way to have it clear automatically, would be to use an Event in some VBA code to achieve a clearance of the cell. You would need to look at Debra's section on using Combo boxes http://www.contextures.com/xlDataVal10.html download the sample file http://www.contextures.com/DataValCombobox.zip and then make amendments to the code to clear the required cells first. For example, Name range J2:J8 as Weekday Name range L2:L8 as Month Enter Weekday in cell N2 and Month in cell N3 and Name range N2:N3 as Choice Change Data Validation on cells B2:B12 to be =Choice Change Data Validation on cells C2:C12 to be =INDIRECT(B2) Within the second of Debra's macros, after Set ws =Activesheet insert the following If Target.Column = 2 Then Target.Offset(0, 1) = "" End If Then, as you go to select Weekday or Month in any cell in the range B2:B12, the value in the adjacent cell in column C will go blank, before you choose the new value from the dropdown that is now appropriate to the option you have selected in column B -- Regards Roger Govier "New2XL" wrote in message ... Hi, I downloaded the sample file, and guess what? This formula also contains a bug. In the file, there are 2 columns, Category, and Item. Select category as Fruits, Select Apple from Item. (Works good). No select Vegetable from Category, and guess what, the item is still displaying apple. You have to click on the list on order to retrieve the vegetable list. My question is, can we clear that "Apple", when we select Vegetable?? Cheers "Roger Govier" wrote: Hi The link you gave from Debra's site was the wrong one http://www.contextures.com/xlDataVal02.html If you follow Debra's instructions from here carefully, it will do just what you want. -- Regards Roger Govier "New2XL" wrote in message ... Hi, I have a requirement which has 2 lists to be populated automatically. The 2nd list value is based on the 1st list. For eg: 1st List 2nd List Fruits (Should display list of all fruits mentioned) Vegetables (Should display list of all veg mentioned) Frozen Items (Should display list of all frozen items mentioned) If i select fruits, it should display only fruits. I have tried to other sites ( http://www.contextures.com/xlFunctions02.html) but there is a bug in it. It does not clear the previous values from the second list. Can someone help, with examples..Appreciated |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Counting Entries in two lists | Excel Worksheet Functions | |||
Excel - need a function to compare lists | Excel Worksheet Functions | |||
Dropdown lists | Excel Discussion (Misc queries) | |||
Comparing 2 Customer Lists to Identify Shared Customers | Excel Worksheet Functions | |||
getting multiple data to populate another worksheet | Excel Worksheet Functions |