Start validation lists at the top.

[smowen]

I have a validation list which is dependent on a value entered in another cell.

I have used a named range and the Indirect function in order to achieve this.

However for some of the lists when I open them, they start at the bottom or middle of the list, which contains blank cells.

I understand this is because excel is using the length of the largest list as the standard length of the other lists, but is there a way of making the lists start at the top when they are opened.

Thanks

[Paul]

Hello smowen,

I'm not sure what you mean by

I understand this is because excel is using the length of the largest list as the standard length of the other lists, but is there a way of making the lists start at the top when they are opened.

Any time I create drop down lists using Data Validation they start from the top of the column (unless I already have something selected from the middle, of course).

Perhaps take a look at: http://www.contextures.com/xlDataVal08.html#Dynamic , and the link to the dynamic named range instructions as well (in that paragraph).

[smowen]

Sorry for the confusion

This example might help:

Cars Food Names Cities
Ford Apple Phil London
Vauxhall Pear
Renault Pizza
Bread
Cake
Beef
Lamb



I have a named range (top row above) and by using the =INDIRECT(SUBSTITUTE(C3," ","_")) formula when Cars are selected the list of cars are shown.

However my problem is when I enter for example Names, the list does not start at the top of the list but half way through.

Is there a way to stop this?