Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Data Validation using Unique Values
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? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Data Validation using Unique Values
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" wrote in message ... 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? |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Data Validation using Unique Values
Awesome!! Thanks very much
"Bernie Deitrick" wrote: 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" wrote in message ... 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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Obtaining Unique Number From Data Validation List | Excel Discussion (Misc queries) | |||
Unique entry - Data Validation | Excel Discussion (Misc queries) | |||
DataValidationList - Unique Entries | New Users to Excel | |||
Unique numbers from data validation list | Excel Worksheet Functions | |||
Data Validation dependant and unique | Excel Worksheet Functions |