Creating a list of aproved/not members

[loopoo]

Hello all!

I need some help on the following issue:

I have a named ranged defined in a worksheet.
I want members from several cells in other worksheet from the same
workbook to be able to recieve values from that list in the range, with
2 exceptions : empty cell or a string "rejected" per example - which
shouldn't be allowed values.
I'm using data validation, but I don't know how to make this happen or
if it's possible with this function.

Thanks in advance,
Chris

 

[Tom Ogilvy]

Can you make second list with the excluded values. This would be the
easiest to use as your source.

 

[loopoo]

Yes , that's possible....but how to mix that???

Sorry, but I'm not so experienced in Excel.

Thanks a lot for your help,
Chris

 

[loopoo]

So now, my problem is that I have 2 lists: one with allowed values,
another with not allowed values (these values can also be found in the
fitrst list) ??

How can I select in a cell only the values that aren't in the second
list??

Thanks in advance for any help,
Chris

 

[Tom Ogilvy]

If you have an acceptable values list, just use that as the source for data
validation.

If you want to allow anything but what is in the not allowed list, then you
could use a custom formula that does a lookup on the not allowed list.
Something like:

=iserror(match(cell with validation,2nd list,0))

 

[loopoo]

Thanks a lot,

The second version is what I needed.

Chris