Remove Blanks In Data Validation Drop Down List (Dynamic Named Range)

my Hello everyone!

I am sure this is easy but I have looked around the entire afternoon and I have not been able to find the correct implementation. Is there a way to create a list, which dynamically expands, to name the list (name range) so that it can be used somewhere else in the workbook and remove the blanks?

I will attach the workbook next.



Many thanks for your support!

Originally Posted by d365b

my Hello everyone!

I am sure this is easy but I have looked around the entire afternoon and I have not been able to find the correct implementation. Is there a way to create a list, which dynamically expands, to name the list (name range) so that it can be used somewhere else in the workbook and remove the blanks?

I will attach the workbook next.



Many thanks for your support!

Here’s the workbook.

In A5
=IFERROR(INDEX($B$2:$B$60,AGGREGATE(15,6,(ROW($B$2:$B$60)/($B$2:$B$60<>""))-ROW($A$1),ROWS($1:1))),"")

copy down

Named range "Project_ID"

Refers to: =OFFSET(Sheet1!$E$5,,,COUNTIF(Sheet1!$E$5:$E$60,"> "),1)

Thanks John for your immediate response. I have seen this implementation but I wish not to re-list the items somewhere else.

Thanks

Thank you ChemistB! I thought there was a way to do that without copying down the list without blanks.

Do I have to copy the list somewhere else even if the the range won’t expand?

Thanks ChemistB!