In Office 2007/2008, I was able to use the following formula to create a list for data validation:
=INDEX(B2:B79,MATCH(TRUE,B2:B79<>B2,0)-1):INDEX(B2:B79,MATCH(TRUE,B2:B79<>B2,0))
My B column contains 2 unique values, so this chose 2 next to each other that had different values. In Excel 2010, I get the error: "You may not use unions, intersections, or array constants for data validation criteria". Does anyone know how to select the two unique values in the form of a list for data validation purposes?
=INDEX(B2:B79,MATCH(TRUE,B2:B79<>B2,0)-1):INDEX(B2:B79,MATCH(TRUE,B2:B79<>B2,0))
My B column contains 2 unique values, so this chose 2 next to each other that had different values. In Excel 2010, I get the error: "You may not use unions, intersections, or array constants for data validation criteria". Does anyone know how to select the two unique values in the form of a list for data validation purposes?