Dynamic range from every nth value in a column in a data validation list

**Xaoc** · 01-07-2013, 09:10 AM

I'm trying to create an array formula that makes a dynamic range from every nth value in a column and use that formula to create a data validation list in a cell. I've managed to conjure a formula that creates a dynamic array from every nth value in a column, but I can't seem to use it in data validation. Excel doesn't seem to like array formulas as a data validation source and using the formula to create a named range also gives an error. I tried to make an array range in a sheet and use that to create a data validation list, but I can't get rid of the #N/A-s even with IFERROR. Also my current formula doesn't work with strings. I'd appreciate if someone would help me make it work with data validation and with strings.
The formula is:

Please Login or Register  to view this content.

See the attached workbook on how it works as an array range

**JosephP** · 01-07-2013, 09:21 AM

that is impossible I'm afraid. a dv list has to be one contiguous row or column so you would have to use the formula in another range to extract the values you want and refer to that range in the dv list

**Teethless mama** · 01-07-2013, 09:39 AM

See the attached

**Xaoc** · 01-07-2013, 12:20 PM

While that allows for DV it doesn't skip the in-between cells, it just rearranges all the cells and excludes the blank ones. In addition it doesn't work with text.

**Marcol** · 01-07-2013, 05:08 PM

Try this workbook

Put your data in column A. This list can be any length, and must be continuous.

Enter the desired increment "n" in D2, the data validation list in E2 will change to suit.