Trying to create a drop down list that updates based on last selection

[DuNandi]

I am trying to create a drop down list that updates based on previous selections. When i say updates, i mean it deletes the selected information from an updated source list. For example:

The source data on sheet 1 is:
A2 Apple
A3 Boy
A4 Cat
A5 Dog
A6 Egg

This are the column headings:
Column C - Day1
Column D - Day 2
Column E - Day 3

I inserted a drop down list using the data validations tools in cell B2 with the sources being $A$2:$A$6

If i select Apple from this drop down list, i would like to create a formula that would remove/delete it from the current list and create one with the remaining four options. I was thinking of creating four different source lists in a separate sheet that could find the data based on the selection from the "cell above" or previously selected cell and delete that value from the list that the source would update and not include that value. Any ideas on what formula i could use?

I hope my questions makes sense.

Cheers
Dunandi

 

[T. Valko]

I am trying to create a drop down list that updates based on previous selections. When i say updates, i mean it deletes the selected information from an updated source list. For example:

The source data on sheet 1 is:
A2 Apple
A3 Boy
A4 Cat
A5 Dog
A6 Egg

This are the column headings:
Column C - Day1
Column D - Day 2
Column E - Day 3

I inserted a drop down list using the data validations tools in cell B2 with the sources being $A$2:$A$6

If i select Apple from this drop down list, i would like to create a formula that would remove/delete it from the current list and create one with the remaining four options. I was thinking of creating four different source lists in a separate sheet that could find the data based on the selection from the "cell above" or previously selected cell and delete that value from the list that the source would update and not include that value. Any ideas on what formula i could use?

I hope my questions makes sense.

Cheers
Dunandi

It's a bit complicated...

http://contextures.com/xlDataVal03.html

 

[VoG]

Maybe like this http://www.contextures.com/xlDataVal03.html