Data Validation using Unique Values

[Hugh]

In column A, I have numbers which are the sum of integer multiples of
numbers ( 3 and 6 in this case) e.g. 9, 3, 9, 12, 3, 9, 12, etc
In column B, I want to use Data Validation to restrict input to one of the
values in column H, but I want the Data Validation drop down to display only
the unique values – 3,9,12.
Can I do this?

 

[Bernie Deitrick]

Hugh,

Yes, you can.

I have assumed that you have a header in cell H1, and values starting in H2

In another cell - let's say J2, enter the array formula (enter using Ctrl-Shift-Enter instead of
just enter)

=INDEX($H$2:$H$1000,MATCH(0,COUNTIF($J$1:J1,$H$2:$H$1000),0))

and copy down column J until you get error values. (Instead of H1000 use the row number of your last
filled in cell. If you extend the range beyond the filled in range, you will get a 0 as one of the
values.)

Then create a named range "allowed" (Inseret / Names / Define ) using the formula

=OFFSET(Sheet1!$J$2,0,0,COUNTIF(Sheet1!$J$2:$J$100,"<>#N/A"),1)
Change the J100 to the last cell that has the INDEX formula.

Then select column B, use Data / Validation List, and for the list source, use =allowed

You must have the = sign included.

HTH,
Bernie
MS Excel MVP

 

[Hugh]

Awesome!! Thanks very much

Hugh,

Yes, you can.

I have assumed that you have a header in cell H1, and values starting in H2

In another cell - let's say J2, enter the array formula (enter using Ctrl-Shift-Enter instead of
just enter)

=INDEX($H$2:$H$1000,MATCH(0,COUNTIF($J$1:J1,$H$2:$H$1000),0))

and copy down column J until you get error values. (Instead of H1000 use the row number of your last
filled in cell. If you extend the range beyond the filled in range, you will get a 0 as one of the
values.)

Then create a named range "allowed" (Inseret / Names / Define ) using the formula

=OFFSET(Sheet1!$J$2,0,0,COUNTIF(Sheet1!$J$2:$J$100,"<>#N/A"),1)
Change the J100 to the last cell that has the INDEX formula.

Then select column B, use Data / Validation List, and for the list source, use =allowed

You must have the = sign included.

HTH,
Bernie
MS Excel MVP