Data Validation from cells Value

[rhombus4]

Normally you can just enter a list of values in the source box e.g.

England, Spain, France, Denmark and it will appear vertically

I have some values in a cell based on a formula, How can I enter these in the Source box so it appears going down in the dropdown instead of across when it refers to a cell

e.g. I enter =C2 in the source and it shows England, Spain, France, Denmark

Rather than
England
Spain
France
Denmark

 

[Fluff]

Enter the values in separate cells, c2 to c4 & then you can use C2:C4 for the data validation.

 

[rhombus4]

Enter the values in separate cells, c2 to c4 & then you can use C2:C4 for the data validation.

That's what I did initially, but the length of the list changes so used name range which is C2:C50

Its ok when the formula has a big list, but if the list is short there are loads of blanks at the bottom. That's why i used a textjoin formula.

Is there a way to get rid of all the blanks for the short data validation lists

 

[Fluff]

You can use a dynamic named range

Excel dynamic named range: how to create and use

Learn how to create a dynamic named range in Excel and how to use it in your formulas so that a formula expands automatically to include new data in calculations.

www.ablebits.com