Drop Down Dead!

[nigelandrewfoster]

I have a drop down list in one of my cells. The table it takes the list from contains a number of unused slots at the bottom (blanks). For some reason, when I click the button to display the list, it starts by displaying the bottom of the table - so you have to scroll UP through the list. This will confuse users as the bottom values are blank, if you understand me. How can I get the list to be displayed from the top (which seems a more logical thing for Excel to do anyway). Thank you for your interest.

 

[gecs]

Name the range you're using as a source for that drop-down using a dynamic range formula, something like:

=OFFSET($A$1,0,0,COUNTA($A:$A))

and use that name in the Data Validation formula (=name).

This will remove the blank rows and the drop-down will be displayed from the top. The drop-down will start displaying with the first match of the cell's value in the list and because the cell is initially blank...

 

[nigelandrewfoster]

Thanks for your very helpful reply. I understand the logic of what you said and I'm sure it will work. However, please can you clarify the first bit? My range is currently called "alpha_prod" and it is defined as ='DIRECT INPUT'!$M$4:$M$196.

 

[gecs]

Using the Name Manager (Ctrl+F3) change the formula (Refers to: ) for the name "alpha_prod" to:

=OFFSET('DIRECT INPUT'!$M$4,0,0,COUNTA('DIRECT INPUT'!$M$4:$M$203))

The range used in the COUNTA function has to be large enough to accommodate the record numbers expected to be added in time - you may change this range to a larger one if you think that the number of items on column M will increase to more than 200 in time. If you have only 5 cells in the 'DIRECT INPUT'!$M$4:$M$203 range, the formula will return the range with only 5 rows, when you add more data into that range (with no blank cells in between) the OFFSET based formula will return a range with those new data included - that's why it is called "dynamic".

 

[nigelandrewfoster]

Thanks, that's great. I had read a little about dynamic ranges, and how they relate to expanding bar charts as you add data, but your explanation is clearer.

 

[nej12]

I came to this website looking for help on this subject. I don't have my file right in front of me at the moment but I'm certain that I use a dynamic range offset for one drop down list and it keeps going to the bottom as well. It suddenly started doing this when I used another type of offset for a list to find a range of cells in 1 column and display in a dropdown - I used to have data in different columns with a bunch of different dynamic range drop-down list and thought it would be easier to have one list column and one formula only which works well.

Is it possible the two types can conflict?

I tried deleting and replacing my Dynamic named range drop-down setup and clearing contents from the cell. It still happens

This is the link/info to the "other" type dropdown I'm using. So would a basic named range dynamic dropdown fight/conflict with a dynamic dependent?

Dynamic (dependent) validation drop down without named ranges

Hi, I need to be able to define a dependent drop down using a lookup with contents based on another column. All the posts I've searched show how to do this by defining different named ranges - eg if there are 6 values in the first column, and you want the second column to show only data from a...

www.mrexcel.com