No Blanks in List for Data Validation

Hi,

I've designed something rather complex for work that I know nobody is going to have the skill set to adjust as needed if I was to leave in the future, so I'm trying to adjust parts of it so it can flex to their needs rather than need any technical adjustment.

I have a Data Validation list. The source may grow, but may reduce, it may grow and then reduce, and vice versa. Ideally, I would like it to only show the values up until the final value.

I have attempted defining it within a name, but it doesn't shrink, only grows. I've attempted via a formula, something along the lines of "=IFERROR(INDEX($A$2:$A$11, SMALL(IF(NOT(ISBLANK($A$2:$A$11)), ROW($A$1:$A$10),""), ROW(A1))),"")" but this didn't work both ways either. I end up with a drop-down box with 4 entries (currently) and lots of blanks below it, which doesn't look great.

Any advice?

I usually do this with a list that starts in A1:
Formula: Select Code copy to clipboard
=OFFSET($A$1,0,0,COUNTA($A:$A))

It returns a list of non-blank cells in column A, assuming there are no embedded blank cells.

But I don't know anything about your data or what your current formula is trying to do.

This works, thank you.

I appreciate I didn't give much about the actual existing data but it's a whole can of worms so wanted to keep it generic. Again, thank you for your help!