View Single Post
  #1   Report Post  
KR
 
Posts: n/a
Default 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.