Decreasing Validation List

[musoguy]

Hi folks, hope someone can help.

I have created a list of 5 people's names using data validation.

To ease the explanation, say I have cells A1 to A5 using this drop down list. Cells B1 to B5 each have a different class in it (as in Math, History etc). The idea is that I can assign each person from the list to a class.

What I would love to happen is if I assign a person to the class in cell A1, then when I go to cell A2, their name disappears from the drop down list and I am left with 4 names in the list and so on.

The actual spredsheet I am creating has a lot more than 5 classes and 5 people, which is why I am concerned about assigning the same person to two classes at the same time by accident.

I did find a solution at http://www.ozgrid.com/Excel/decreasing-list.htm, but for some reason it didn't seem to work, and I'm not sure what I'm doing wrong as I copied their example to see if I could get it to work and it didn't seem to.

I'm using Excel 2010 if that makes a difference.

Thank you so much in advance

 

[musoguy]

Scratch that, the solution I added a link to does work, I just had my named range on a different worksheet. Hope it can be of use to someone else

 

[musoguy]

I take it back! I do have a problem!

The way the VBA code works is it deletes the name from the list I created the named range from. Which means if I decide to change a teacher, the other person's name doesn't reappear in the list, it is gone.

Is there anyway to create a non-destructive solution? I need the original list to remain untouched as it is used in more than one column.

 

[tusharm]

For a solution that applies to a userform see below. Maybe, you can adapt it to data validation lists.
Extend an object’s attributes to coordinate multiple userform controls
http://www.tushar-mehta.com/publish...to manage multiple controls in userform.shtml

I take it back! I do have a problem!

The way the VBA code works is it deletes the name from the list I created the named range from. Which means if I decide to change a teacher, the other person's name doesn't reappear in the list, it is gone.

Is there anyway to create a non-destructive solution? I need the original list to remain untouched as it is used in more than one column.

 

[T. Valko]

Hi folks, hope someone can help.

I have created a list of 5 people's names using data validation.

To ease the explanation, say I have cells A1 to A5 using this drop down list. Cells B1 to B5 each have a different class in it (as in Math, History etc). The idea is that I can assign each person from the list to a class.

What I would love to happen is if I assign a person to the class in cell A1, then when I go to cell A2, their name disappears from the drop down list and I am left with 4 names in the list and so on.

The actual spredsheet I am creating has a lot more than 5 classes and 5 people, which is why I am concerned about assigning the same person to two classes at the same time by accident.

I did find a solution at http://www.ozgrid.com/Excel/decreasing-list.htm, but for some reason it didn't seem to work, and I'm not sure what I'm doing wrong as I copied their example to see if I could get it to work and it didn't seem to.

I'm using Excel 2010 if that makes a difference.

Thank you so much in advance

There's a formula method described here:

http://contextures.com/xlDataVal03.html

I would use slightly different formulas but the net effect is the same.

 

[musoguy]

Thank you both very much for taking the time reply. I ended up going with T. Valko's solution, it was easier for a relative newbie.

Thanks again

 

[T. Valko]

Thank you both very much for taking the time reply. I ended up going with T. Valko's solution, it was easier for a relative newbie.

Thanks again

You're welcome. Thanks for the feedback!