Not showing Blank Spaces in a Drop Down List

[Bob Folks]

Hi People

I have a sheet that I am developing that has several drop down lists that need to be somewhat dynamic. I have them in a Named ranges for ease of adding new information and removing old that is required from time to time. My question is how do I stop excel from showing the empty cells within the range, I have left the ignore blank option checked. Failing this how do I force the drop down to start at the top of the list every time not at its last saved possition.

Thanks in advance

Bob

[BadlySpelledBuoy]

Make the named ranges dynamic.

=$A$1:INDEX(sheet1!$A:$A,COUNTA(sheet1!$A:$A))

If you set up a named range with the above as the range details, it will expand and contract as you add/remove data, on the proviso there are no blank cells in the range.

Obviously change the sheet and column details to fit your data.

If you need more help post a sample workbook.

BSB

[Bob Folks]

You sir are a Legend thankyou

[Glenn Kennedy]

Here's a different approach.

[BadlySpelledBuoy]

Pleasure is all mine. Happy to help.

Don't forget to mark the thread as SOLVED if you're happy you have a working solution.

BSB.

[Glenn Kennedy]

Too late - ah well!!

[BadlySpelledBuoy]

Too late - ah well!!

Makes a change for me to beat you to an answer!