Populate lists automatically

[Guest]

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

 

[Roger Govier]

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.

 

[Guest]

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]

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

 

[Guest]

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]

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","")