Deleting items as they are used from a drop down menu

I have a column that has 44 active rows in it and I have attached each cell
in those 44 rows to a data validation list.

I would like to know how to tell Excel, that if an item is selected from the
drop down list, that it is removed from the choices and conversely if a
person deletes the chosen item from one of the 44 cells, it is placed back
into the list for choices.

I am guessing a Visual Basic macro is going to be employed here and I don't
mind if the drop down list gets moved from the workbook and placed straight
into the coding of the macro.

I thank you for any help you can offer.

Tofer

I searched the forum here and found this website which helped me solve the
duplicate value problem. So thanks for the help you gave me, whoever you are
at that website. (I obviously cannot remember the name of the person.)

http://www.beyondtechnology.com/tips.shtml

Now I am wondering if anyone has a way to delete items from the lookup list
if they have been used, as well as a way to put the items back in the list if
they are deleted from the 44 cells.

Thanks, Tofer

"ToferKing" wrote:

> I have a column that has 44 active rows in it and I have attached each cell
> in those 44 rows to a data validation list.
>
> I would like to know how to tell Excel, that if an item is selected from the
> drop down list, that it is removed from the choices and conversely if a
> person deletes the chosen item from one of the 44 cells, it is placed back
> into the list for choices.
>
> I am guessing a Visual Basic macro is going to be employed here and I don't
> mind if the drop down list gets moved from the workbook and placed straight
> into the coding of the macro.
>
> I thank you for any help you can offer.
>
> Tofer

You could place your list of choices in a column and in the next column something like

=IF(COUNTIF(A$1:A$44,E1)>0,"",E1)

and fill down alongside the list. Here A$1:A$44 is your data entry range (where the user makes selections), values are in column
E (from E1 down) and the formula above is in column F.

Use Data >> Validation >> List and select column F as the "source"

The only drawback is you get a list with "holes" (does not compact as items are removed).
If you don't like that then enter this in G1 and fill down:

=IF(ISERROR(SMALL(F$1:F$44,ROW())),"",SMALL(F$1:F$44,ROW()))

If your list doesn't start at row 1 then you'll need to adjust the ROW() part.

Use this third column as your validation "source".

I'm sure there are niftier ways to do this though....

Tim



"ToferKing" <[email protected]> wrote in message news:[email protected]...
>I have a column that has 44 active rows in it and I have attached each cell
> in those 44 rows to a data validation list.
>
> I would like to know how to tell Excel, that if an item is selected from the
> drop down list, that it is removed from the choices and conversely if a
> person deletes the chosen item from one of the 44 cells, it is placed back
> into the list for choices.
>
> I am guessing a Visual Basic macro is going to be employed here and I don't
> mind if the drop down list gets moved from the workbook and placed straight
> into the coding of the macro.
>
> I thank you for any help you can offer.
>
> Tofer

Tim,

Thank you so much for the ideas. I will try them today.

Christina

"Tim Williams" wrote:

> You could place your list of choices in a column and in the next column something like
>
> =IF(COUNTIF(A$1:A$44,E1)>0,"",E1)
>
> and fill down alongside the list. Here A$1:A$44 is your data entry range (where the user makes selections), values are in column
> E (from E1 down) and the formula above is in column F.
>
> Use Data >> Validation >> List and select column F as the "source"
>
> The only drawback is you get a list with "holes" (does not compact as items are removed).
> If you don't like that then enter this in G1 and fill down:
>
> =IF(ISERROR(SMALL(F$1:F$44,ROW())),"",SMALL(F$1:F$44,ROW()))
>
> If your list doesn't start at row 1 then you'll need to adjust the ROW() part.
>
> Use this third column as your validation "source".
>
> I'm sure there are niftier ways to do this though....
>
> Tim
>
>
>
> "ToferKing" <[email protected]> wrote in message news:[email protected]...
> >I have a column that has 44 active rows in it and I have attached each cell
> > in those 44 rows to a data validation list.
> >
> > I would like to know how to tell Excel, that if an item is selected from the
> > drop down list, that it is removed from the choices and conversely if a
> > person deletes the chosen item from one of the 44 cells, it is placed back
> > into the list for choices.
> >
> > I am guessing a Visual Basic macro is going to be employed here and I don't
> > mind if the drop down list gets moved from the workbook and placed straight
> > into the coding of the macro.
> >
> > I thank you for any help you can offer.
> >
> > Tofer
>
>
>

Tim, your solution worked!

Thank you ever so much.

(By the way, I have been trying to click on your answer and marking it as a
soluction, but the message window isn't giving me that option.)

Christina

"Tim Williams" wrote:

> You could place your list of choices in a column and in the next column something like
>
> =IF(COUNTIF(A$1:A$44,E1)>0,"",E1)
>
> and fill down alongside the list. Here A$1:A$44 is your data entry range (where the user makes selections), values are in column
> E (from E1 down) and the formula above is in column F.
>
> Use Data >> Validation >> List and select column F as the "source"
>
> The only drawback is you get a list with "holes" (does not compact as items are removed).
> If you don't like that then enter this in G1 and fill down:
>
> =IF(ISERROR(SMALL(F$1:F$44,ROW())),"",SMALL(F$1:F$44,ROW()))
>
> If your list doesn't start at row 1 then you'll need to adjust the ROW() part.
>
> Use this third column as your validation "source".
>
> I'm sure there are niftier ways to do this though....
>
> Tim
>
>
>
> "ToferKing" <[email protected]> wrote in message news:[email protected]...
> >I have a column that has 44 active rows in it and I have attached each cell
> > in those 44 rows to a data validation list.
> >
> > I would like to know how to tell Excel, that if an item is selected from the
> > drop down list, that it is removed from the choices and conversely if a
> > person deletes the chosen item from one of the 44 cells, it is placed back
> > into the list for choices.
> >
> > I am guessing a Visual Basic macro is going to be employed here and I don't
> > mind if the drop down list gets moved from the workbook and placed straight
> > into the coding of the macro.
> >
> > I thank you for any help you can offer.
> >
> > Tofer
>
>
>