Exclude item in list from data validation?

I can't speak for better options, perhaps one of the other contributors has a more elegant solution. Here is the best I could come up with.

In a test worksheet, fill A2:A12 with sequential letters (A2 =A, A3=B, etc)

In column B2:B12 fill in any individual values you want to exclude from your displayed list (e.g. D2 = D, D3 =J)

In C2, enter this array formula:

=OFFSET(A2,SUM(COUNTIF(A$1:A2,B$2:B$10))+SUM(COUNTIF(OFFSET(A2,SUM(COUNTIF(A$1:A2,B$2:B$10)),),B$2:B$10)),)

(enter array formulas by pressing Ctrl-Shift-Enter)

Drag down to cover the same size range as your original data.

This gives you the same list, in the same order, without the excluded values. 

 I'm making some assumptions about your comfort level with creating that final offset range (without the blank cells at the end) to feed your data validation, but if you have trouble with it, just post back.

Note: data validation only accepts same-page direct references, so if you move the helper column to a different worksheet, be sure to make your final list a named range, then feed that named range into the data validation.

HTH,

Keith

At this time, it appears no - unless I move the helper column to another worksheet, which preferably I would not like to - but if that is the only solution - then we can make it happen.  But from reading the above, I am guessing that may be our only option, correct?

I had thought that setting a named range consisting of the two source ranges would be seen as a contiguous set of data by data validation, but upon further testing, that doesn't seem to be true.

Does your worksheet design allow for the use of a helper column?

Thank you for the reply - I do want to pluck out a certain value that may change places in the range. Unfortunately, using the above formula to begin playing around with it, I am getting a message that I cannot use reference operators or array constants for Data Validation criteria??

Is there a way to have this exclusion as part of a data validation on a cell, or would I need to do some sort of range sorting before and have it feed the data validation? If so, how would I do a "sort/exclude" on a range to result in the omission of the one value?

Thank you again, I really appreciate it.

You can specify a discontiguous named range with a comma in XL2007 and earlier, I don't have 2010.

=Sheet7!$B$1,Sheet7!$B$3

If data validation won't take a discontiguous range directly, then define it as a named range, and use that for validation.

If you know your excluded item will always be in position 4, then you can use something like

=OFFSET(vbList,0,0,3,1),OFFSET(vbList,4,0,MAX(1,vbCount)-4,1)

If your excluded item might change position, then you'll need to use MATCH to find it, and adjust the above formula accordingly.

HTH,

Keith