Data Validation using Unique Values

H

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?
 
B

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
 
H

Hugh

Awesome!! Thanks very much

Bernie Deitrick said:
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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top