Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #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.


  #2   Report Post  
Bryan Hessey
 
Posts: n/a
Default


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

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Data Validation gbeard Excel Worksheet Functions 1 May 3rd 05 09:09 AM
Pull Current Month's Data Out of List - Repost Karl Burrows Excel Discussion (Misc queries) 4 May 3rd 05 01:06 AM
data validation gbeard Excel Worksheet Functions 2 May 2nd 05 09:57 PM
Named dynamic ranges, copied worksheets and graph source data WP Charts and Charting in Excel 1 November 28th 04 06:19 PM
Data Validation List Option Affecting Other Cells? tomrobs Excel Worksheet Functions 1 November 5th 04 05:26 PM


All times are GMT +1. The time now is 12:39 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"