Create Data Validation Fixed Range but remove blanks

[Yecart77]

Hi,

Sample File in One drive

My sample file is in 365 but I'm needing to create this in Excel 2016.

Data source for data validation drop down list needs to be from A3:A28 as the list is dynamic and more values may populate in currently empty cells.

Question is how do I create the data validation list that just displays the cells that have values and drops the cells underneath that are empty.

If data came into cells D7, D8 etc the drop down display would reflect these extra values.

Thank you in advance for your time.

Cheers,
Tracey

 

[awoohaw]

I'm not sure this will work for you but it will for me. If you have a calculated NAMED RANGE it may work for you.
This is the calculated named range:

=$A$3:INDEX($A$3:$A$15,SUM(--($A$3:$A$15<>"")),0)

The items in column F are not needed for me. Just create the name with the formula above. I think it will work for you.

** NOTE: This requires no blanks between items in the list.

 

[Yecart77]

Thank you awoohaw! That worked like a charm. Thank you for saving me more hours of frustration.

 

[awoohaw]

I am happy to help.

Best wishes!