Filtering Lists

[robertwhite]

I have a list of data like:
1 A X
1 B Y
1 A Z
2 B Y
I am trying to create dropdown lists which would show 1 and 2 for the first cloumn, A and B for the second and X, Y and Z for the third. When the user selects A for column 2, I want the list for the list for the first column to only show 1 and then second cloumn to only show X and Z.

Essentially, I am trying to replicate filtering but I only want the filtered lists to show possible combinations.

Any help is much appreciated.

Thanks
Robert

 

[RoryA]

There is no built-in option for that and I think it would be pretty complicated to do in VBA. It would probably also grind your workbook to a halt if you had a lot of data. I look forward to someone proving me wrong though!

 

[Richard Schollar]

Hi Robert

I'm really not understanding - isn't this already what Autofilter does? Or is it a case you only want unique combinations returned, so multiple 1 A X would be excluded?

 

[Gerald Higgins]

I think you can do this, but you need to be absolutely clear about what the possible combinations actually are.

Also, the way you have described the problem makes me think that these lists are somehow self-referencing - i.e. they allow you to enter what's already been entered, but not what hasn't already been entered. Is that correct ? If so, you need to be very clear how that works.

At a very basic level, you can do something like this . . .

Cell A1
Set data validation to allow entries from list, source A, B.

Cell B1
Set data validation to allow entries from list, source cell C1.

Cell C1

=if(a1="A",1,2)

This makes the list of allowable values in B1 dependent on whatever value you have already keyed into A1.

But I'm guessing you might want something more complicated.

 

[Gerald Higgins]

Sorry I may have misunderstood the OP.

Re-reading it, I think Richard S is right - this sounds like basic Data Filter

 

[robertwhite]

Hi Richard

I am trying to do two thing, ther first of which autofilter does i.e. create unique lists. The second thing I am trying to do is update the lists after a selection is made in another list. For example if Y is selected in cloumn 3, I no longer want selscting A to be an opiton for cloumn 2. Autofilter would offer A and B as opitons.
Thanks
Robert

 

[Gerald Higgins]

Autofilter would offer A and B as opitons.

No it wouldn't. Try it.

 

[robertwhite]

Do you have a new brain handy.
Thanks
Robert