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? |
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? |
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? |
All times are GMT +1. The time now is 01:30 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com