Hi, thanks for reading, I would like to illustrate an example. I have one column and say 5 rows. On each row is a combo box the user can click, and choose from "Vanilla", "Chocolate" and "Strawberry" for example. How can I prevent the user from selecting a value that has already been used? For example, let's say in the first row the user picked "Vanilla." Now, in the second row the user should NOT be able to pick Vanilla, and only "Chocolate" or "Strawberry" would be allowed. Is there an easy way to do this...?????? I've tried VLookUP, Match, etc. but I'm at a loss now...
Results 1 to 6 of 6
-
Star Lounger
- Join Date
- Aug 2002
- Location
- Michigan, USA
- Posts
- 52
- Thanks
- 0
- Thanked 0 Times in 0 Posts
2004-11-03 18:37Look up/prevent duplicates in a column (2002)
-
Subscribe to get a FREE chapter from Windows 7 The Missing Manual
This month, every Windows Secrets subscriber can download a one-chapter excerpt of Windows 7: The Missing Manual.Windows 7: The Missing Manual provides valuable information to help you overcome these difficulties in learning a new operating system. Subscribe today to download your free excerpt.
-
Platinum Lounger
- Join Date
- Feb 2001
- Location
- Weert, Limburg, Netherlands
- Posts
- 4,812
- Thanks
- 0
- Thanked 0 Times in 0 Posts
2004-11-03 19:04Re: Look up/prevent duplicates in a column (2002)
Use data, validation for that. Use the custom type and For the formula use
=COUNTIF($A$1:$A$5,"=" & A1)<=1
(cell A1 has to be active when defining the validation and the list extends to cell A5)Jan Karel Pieterse
Microsoft Excel MVP, WMVP
www.jkp-ads.com
Professional Office Developers Association
-
Star Lounger
- Join Date
- Aug 2002
- Location
- Michigan, USA
- Posts
- 52
- Thanks
- 0
- Thanked 0 Times in 0 Posts
2004-11-04 20:15Re: Look up/prevent duplicates in a column (2002)
Thank you kindly for your reply! Ummm, I hate to admit this, but I already used Data Validation to limit the input in each cell to "List." Is there any other work around that you can think of? Thanks again for your help, I appreciate it! <img src=/S/smile.gif border=0 alt=smile width=15 height=15>
-
Uranium Lounger
- Join Date
- Jan 2001
- Location
- South Carolina, USA
- Posts
- 7,295
- Thanks
- 0
- Thanked 0 Times in 0 Posts
2004-11-04 20:40Re: Look up/prevent duplicates in a column (2002)
You could probably use the worksheet change event routine. If one of those 5 cells is changed, then modify the list of what displays in the drop down list so that used values can't be chosen for the other cells.
Legare Coleman
-
Star Lounger
- Join Date
- Feb 2003
- Posts
- 89
- Thanks
- 0
- Thanked 0 Times in 0 Posts
2004-11-05 08:36Re: Look up/prevent duplicates in a column (2002)
(Edited by HansV to make URL clickable - see <!help=19>Help 19<!/help>)
This maybe of interest to you...
http://www.contextures.com/xlDataVal03.html
-
Star Lounger
- Join Date
- Aug 2002
- Location
- Michigan, USA
- Posts
- 52
- Thanks
- 0
- Thanked 0 Times in 0 Posts
2004-11-05 14:53Re: Look up/prevent duplicates in a column (2002)
Thanks again for everyone's help. Maxflia10, now THAT is quite a find. It took me hours to finally map it to my spreadsheet, and I finally have it working. Thanks for sharing this link. By the way, this is the most AMAZING excel formula I personally have ever seen in my life - the complexity and "trickery" is just pure genius... Problem Solved 95%. Why not 100%? Because in some circumstances I actually need to allow a duplicate "N/A" entry to be selected multiple times (and thus do not want it to disappear), but I'm sure I can tweak it later...
THANK YOU!!!!!!!!
