LinkBack URL
About LinkBacks
Is it possible to exclude values that are already "somewhere in some cell" from the list of possible option?
Is it possible to exclude values that are already "somewhere in some cell" from the list of possible option?
The way to do this is to use a named range for the list, and manage what appears in that range as values are taken. But we need much more detail to give you a specific solution. Best thing would be attach your file, and explain how values get "taken."
for example....
In cell B2 you will be able to chose "Seinfeld" then you go to B3 and you are still able to chose "Seinfeld" even though it is already been taken in B2.
I would like in B3 to only have choice
Costanza
Benes
Kramer
David
and so on.
Are you interested in a formula solution or VBA code?
Here is a formula solution. I have added a few columns in sheet myList.
First: First name of each person. You didn't ask for this but it allows a lookup of the first name based on last name.
Index: A sequential index for each name in the full list of names.
Not Selected Index: A sequential index for each name in the list that has not yet been selected.
Remaining: The names that have not yet been selected.
I added a named formula AvailableNames to return the list of name that have not been selected, to use in Data Validation. This replaces your list of all names.
Now in Sheet1, any time a name is selected in any of the cells in column B, that name is removed from the AvailableNames list so other selections do not include names that have already been selected.
pretty cool. Let me play with this and see if I can tweak it
Thank you so much
you have in the list source "=AvailableNames". Where is this coming from?
can you remove first name look up please. I am not sure which p[art to remove from formula
This is a named formula, sometimes called a dynamic named range. If you go to Formulas > Name Manager you will see how it is defined. It starts with the first remaining name, then includes the non-blank names.Originally Posted by stribor40
The formula for looking up the first name is a VLOOKUP. Just delete the whole formula and go back to what you had before, which was just the names typed in.
Try this...
Data Range
E1:E5 is the original list
Enter this array formula** in F1 and copy down to F5:
=IFERROR(INDEX(E$1:E$5,SMALL(IF(ISNA(MATCH(B$2:B$6,E$1:E$5,0)),ROW(E$1:E$5)),ROWS(F$1:F1))),"")
** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.
Then, as the source for the drop down lists use:
=OFFSET(F$1,,,COUNTIF(F$1:F$5,"?*"))
Biff
Microsoft MVP Excel
Keep It Simple Stupid
Let's Go Pens. We Want The Cup.
Tony, I couldn't get your solution to work. I like the idea of a single column rather that multiple helpers, but see if you can tell what I did wrong here. One of the selected items still shows up in the remaining items list.
Argh!
My bad. That isn't the correct formula for this application.
Try this array formula** entered in F1 and copied down:
=IFERROR(INDEX(E:E,SMALL(IF(COUNTIF(B$2:B$6,E$1:E$5)=0,ROW(E$1:E$5)),ROWS(F$1:F1))),"")
** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.
Ah, much better. Thanks!
----------
Is there simple or more readable way to enter these formula...
=IFERROR(INDEX(E:E,SMALL(IF(COUNTIF(B$2:B$6,E$1:E$5)=0,ROW(E$1:E$5)),ROWS(F$1:F1))),"")
It is really hard to follow these function. is it possible to enter formulas in excel by using semicolons one function at the time?
No, you cannot enter formulas like that. This is the only way this function can be entered. What you are doing it a little complicated so it needs a formula that is a little complicated.
We can break down the formula and explain how it works, but you cannot break down this formula into pieces.
I am having troubles with adding source. Error is "Source Currently evaluates to error". I attached my example. Is there a way you can take a look at this please.
The current source formula is based on the data being TEXT.
The data in the sample file is numbers.
Use this as the source formula:
=OFFSET(M$1,,,SUMPRODUCT(--(M$1:M$7<>"")))
It will handle both text and numbers.
Maybe this will be of some help. It uses a similar approach to Tony but will handle both text and numbers.
<---------If you like someone's answer, click the star to the left of one of their posts to give them a reputation point for that answer.Ron W
This works but only on smaller set of data like example i attached above (1111,2222,3333....) but what I found is that on bigger set of data (my example has 600 entries) I would pick a number from pull down and then go one cell below to pick another and number from above would still be available.
To work arround i have to give it some time, click in couple other cells and sometimes force save on a file and then number would not be available any more.
This is set up to handle 1999 entries. I setup the lists on Sheet2.
Last edited by newdoverman; 04-24-2016 at 08:10 PM.
Is it possible to do same with 10,000 entries?
Yes. In all the formulae on Sheet2 change the 2000 to 10,000 or whatever you need.
This is the same worksheet setup to handle 11,999 rows.
I can't imagine using a Drop-Down list with 10,000 + entries.
Here's a sample file that demonstrates my formula.
How many items in pull down you can have with your solution?
Would you be able to clean up this file please. I am really lost what user interface and whats the behind the scene work. Why does the main (user entry) sheet have associated values?
As many as a data validation list will allow (which I don't know right off the top of my head).
The "associated value" is just a value to be returned when a value is chosen from the drop down list. If you don't want to return a value, the column can be deleted.
Last edited by newdoverman; 04-28-2016 at 06:04 PM.
Some quick testing reveals that a data validation drop down list can have up to 32,767 selections.
Would you be able to clean up this file please. I am really lost what user interface and whats the behind the scene work. Why does the main (user entry) sheet have associated values?
is there a way that as you type number that suggestions are shown.....
Lets say my pull down list is....
100
101
102
103
..
..
199
200
201
220
as soon as I start typing up 2 all choices starting with 1 will be skipped?
Kind of kludgy but it works...
You would have to list the source something like this:
Data Range
Then, suppose you want to make a selection that starts with 2, type 2 in the cell, don't press the Enter key, click the drop arrow and the list will automatically scroll to the start of the 2 series of selections.
Tony this formula....=IFERROR(INDEX(E:E,SMALL(IF(COUNTIF(A$1:A$7,E$1:E$7)=0,ROW(E$1:E$7)),ROWS(F$1:F1))),"")
Once I select the value and try to save it excel is still processing. My machine has 2 processors and it is still running and trying to go trough the list to eliminate that choice I just entered. My list has 10,000 values
That's quite a big list.
I've never experienced a list that big so I don't know what to expect as far as processing time.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks