+ Reply to Thread
Results 1 to 2 of 2

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

  1. #1
    KR
    Guest

    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.



  2. #2
    Forum Contributor
    Join Date
    03-13-2005
    Posts
    6,196
    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)

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1