named range, data validation: list non-selected items, and new added items
 

Greetings all-

I'm using Excel2003/Win2000

I have a named range on Sheet2 that has a list of names

I have a bunch of non-contiguous cells on Sheet1 that all have data
validation that select from that list of names

Two questions:

(1) I want to use a range of cells at the bottom of Sheet1 to show any names
from the list that were /not/ used in any of the data validation cells. Is
there a straightforward way to do this without writing a separate formula
for each name? I'd like the names to show up in adjacent cells, e.g.:
unused:
Name 7
Name 18
Name 31
and have that list automatically update as names are used (or replaced) so
if someone then selects Name18 in a data validation cell, the list of names
here would change to:
unused:
Name 7
Name 31

(2) Is there any way to list (for the data validation cells) any name that
was used (typed in) that /isn't/ on the data validation list? That will make
it easier to recognize when someone has added a name so I can go add it to
the list in that the named range calls (or better, put that formula in the
data validation list cells, so it updates automatically when a new name is
added, so it automatically becomes available to the rest of the data
validation cells)

I appreciate any advice or suggestions or formula examples!
Thanks,
Keith

--
The enclosed questions or comments are entirely mine and don't represent the
thoughts, views, or policy of my employer. Any errors or omissions are my
own.

Whilst I hope that someone assists you with a formula it's not looking
promising so far.

The only way I can see this check could be performed is a somewhat
manual task of Copy-ing all the used names from Sheet1 to Sheet3 with a
Paste Special, Values, sort and create a second name-range Lookup
table.
Then on Sheet 2, for each line (use a cell to the right of the table)
do a lookup if a match is found from a Sheet2 item to the newly created
Sheet3 lookup table.

For the second part of your question, if you then copy the Sheet2 data
to Sheet4 and Paste Special, Values, and sort Sheet4 you will have a
list of what is used and what is missing.

It's not much, but will allow you to view what is/is-not a matching
entry, albeit as a manual check.

And as you say "The enclosed questions or comments are entirely mine
and do not represent the thoughts, views, or policy of my employer or
any other individual or group. Any errors or omissions are my own"
(I like that)


--
Bryan Hessey
------------------------------------------------------------------------
Bryan Hessey's Profile: http://www.excelforum.com/member.php...o&userid=21059
View this thread: http://www.excelforum.com/showthread...hreadid=381757