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

 

[just_jon]

A bit more info - and a sample of your data, using Colo's HTMLMaker at the bottom of the page - would help.

 

[loopoo]

Thanks for your help...but this should be on production and not all the users can install an add-in just for that.

Maybe there's a easier way to do that, I hope.


Thanks in advance for any help

 

[just_jon]

Thanks for your help...but this should be on production and not all the users can install an add-in just for that.

Maybe there's a easier way to do that, I hope.


Thanks in advance for any help

No, I meant use the add-in to upload a screenshot of your data so we can better see your layout; or describe it in more detail, giving ranges/rows/columns/etc

 

[loopoo]

Sorry for this missunderstanding.

So, in fact I have 2 lists : the first is with all the possible members, and the second one is with members(this members are also in the first list) not allowed to be selected in the current cell.

So, what I need, is to be able to make a selection from the members in the first list, but only those that aren't present in the second list (with members that should be rejected)

Thanks for your help,
Chris

 

[just_jon]

Book1
	A	B	C	D	E	F	G	H
1	All Members	Not Allowed
2	Aladin	Jon	Brian
3	Brian	Juan	Data Validation, via List
4	Juan		=ISNUMBER(MATCH(C1,AM,0))*ISNA(VLOOKUP(C1,NA,1,0))
5	Smitty
6	Jon
7	Kristy
8
9	named range: AM
10	named range: NA
11
Sheet1

You might use something like the above.

Create 2 named ranges from your 2 lists - they can be on any sheet.

Then use data validation in your input cell as shown above.

It should restrict entry to those names in the 1st list that are not present in the 2nd.

 

[loopoo]

Hi again Jon!

I tried your solution but there seems to be a small problem: when I type the formula in the validation field, after selecting list, Excel gives me the following error:
"The List Source must be a delimited list, or a reference to a single column"

Does anyone have any idea on how to skip this error??

Thanks a lot,
Chris

 

[just_jon]

Hi again Jon!

I tried your solution but there seems to be a small problem: when I type the formula in the validation field, after selecting list, Excel gives me the following error:
"The List Source must be a delimited list, or a reference to a single column"

Does anyone have any idea on how to skip this error??

Thanks a lot,
Chris

How did you define the 2 named ranges, and exactly what did you enter in the Source box for DV?