Drop Down Dead!

nigelandrewfoster

Well-known Member
Joined
May 27, 2009
Messages
747
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.
 

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.

gecs

Active Member
Joined
Jan 26, 2009
Messages
320
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

Well-known Member
Joined
May 27, 2009
Messages
747
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

Active Member
Joined
Jan 26, 2009
Messages
320
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

Well-known Member
Joined
May 27, 2009
Messages
747
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.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,163,421
Messages
5,831,520
Members
430,075
Latest member
Francis101

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top