Drop-down list starting from middle

Hello all,

I've got a lot of drop-down lists in my file. When you click on the little arrow, I'd like the options to show up starting from the first option in my list. Right now they are starting from the middle of the list (example: The drop-down in B1 refers to drop-down list defined as A1:A10. A1:A5 of the drop-down list has items filled in, and A6:A10 are left blank so I can fill in more options later; the arrow activating the drop-down list in B1 shows options starting at A6. I'd like it to start showing the options at A1).

Is there an option so that the drop down will start at the first cell included in the list?

Thanks,
smalls

There are some cool "tricks" for making the drop down lists expand themselves without manual adjustment and NOT leave any blanks.

The easiest way is to have your lists on a separate resource sheet, I call my resource sheet LISTS. Then I used NAMED RANGES to define my list.

So, on sheet LISTS I put some values in column A.
Then select Insert > Name > Define
In the top bar enter a name for this list, like OPTIONS.

In the Refers To: bar insert this dynamic expansion formula:

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


Then in the cell where you want the drop down, use Data > Validation> Allow: List > Source: =OPTIONS.

This named range will expand itself as you add items into column A on your resource sheet.

I would use a single column for each list, not colored boxes. If you want to be clear what each list is for, put the name of the list in row 1 and start the dynamic listing at row2:

=$A$2:INDEX($A:$A,COUNTA($A:$A),1)

Do this once and it will work for you flawlessy forever. I wouldn't use any other technique.

NOTE: As long as you manually create name ranges with blanks in them, then when you use that name in a DV list it will always show you the current selection...and when the cell is empty that's a blank so it will show you a blank row. That's just the way it is.

Which is why I use dynamic name ranges to eliminate this problem once and for all.

You are correct. Dynamic Named ranges cannot be indirectly referenced. It simply won't work. You'll have to decide what gives you the most functionality.

Bummer! Well, thank you for confirming.

I ended up finding a solution, though--I inserted a blank cell into the top of all my lists, and so the drop-down menus start from there instead. The first cell is blank, but at least the choices all show up, which is what I needed for my (admittedly non-excel-expert) users. I used the entire column as the list, as suggested by JBeaucaire, so that there is lots of space for new options.

Cheers and thanks for the help, all.

Actually, you don't have to choose. INDIRECT() can be replaced with a dynamic formula IN the DV formula itself to pull all the items out if you put all your lists together into one sorted list.

All that is needed is that the first column be sorted ascending and have an entry for each final "item" you want to select.

The attached workbook uses dynamic name ranges to create a range for Category and Categories and Items. The INDIRECT() has been taken out and now it works fine.

It even allows for a default message to be used if the Type column is accessed while the Category is empty.

The technique is explored more on my website here.

As you add to you master list later, just add to the bottom and sort.

Add them at the bottom, as many as you wish, then highlight both columns and sort. Easy-peezy.