Dynamic Validation List using a qualifier column in Excel 2016

If the option
1: x
2: x
3:
4: x
5: x
6: x
7:
8:
9:
is possible then you have to create extra range with dynamic list.

Here is an implementation. The first two columns are like what you posted. The third column is a helper column. The fourth column is the dynamic list. The yellow cell is the cell with data validation.

Wow, that works great! I may reply with questions as to how it works after I look at it more closely.

Thanks 6StringJazzer!

Originally Posted by 6StringJazzer

Here is an implementation. The first two columns are like what you posted. The third column is a helper column. The fourth column is the dynamic list. The yellow cell is the cell with data validation.

Wow, that works great! I may reply with questions as to how it works after I look at it more closely.

Thanks 6StringJazzer!

Sure.
The helper column counts the number of X's up to that row. The first x will be assigned 1, the second x is 2, etc.
The dynamic list looks up the number that is the same as its own row number, so in the first row it looks for (the first occurrence of) 1, the second row it looks for 2, etc. Then it returns the value of column B in the same row where it found the number.
The data validation list just looks at that final list. It also uses the OFFSET function to take advantage of using the "height" argument to specify how many items are actually in the list. That way you don't get a few items followed by a bunch of blank lines.