Dynamic Drop-Down Menu?

Question

I read a post a while back of what I think was called a Dynamic Drop-Down Menu. 

I couldn't find it, nor could I pinpoint it online. 

It had the ability to remove any blank cells from the Validation. But when there was something in the cell, it automatically made sure that the Dropdown menu had all of the information in the Cell.

Example..

A1:A5 was what the Validation List was Ranged. But only A1:A2 had text in the cells.

The Dynamic Menu was able to make it so that only the cells for A1:A2 showed up in the Drop-Down List.

Anyone by chance know where I can find the tutorial on how this was done?

Answer 1

You could use the formula

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

in the definition of the MyList name.

Answer 2

Activate the sheet with the validation list.

On the Formulas tab of the ribbon, in the Defined Names group, click Define Name...

In the Name box of the New Name dialog, enter MyList or whatever you'd like to call the list (the name must be a single word).

In the Refers to box, enter the following formula:

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

Click OK (Excel will automatically add the sheet name to the cell references in the formula).

The above version assumes that the list is a contiguous area starting at cell A1, and that the rest of column A is empty.

If you wish, you can limit the list to a maximum number of items, e.g. 20:

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

In the Data Validation dialog, set Allow to List and the Source to =MyList (using the name you assigned).

Answer 3

Worked like a charm. 

Is there a way that it will allow the very top Cell to be blank?

At the moment, my list has the very Top Cell as a blank cell. And there are 5 names listed. The 5th name does not show because of the blank cell.

Answer 4

Hi,

You may refer to my solution at the following link on my website - http://www.ashishmathur.com/blanks-appearing-in-source-data-not-to-appear-in-data-validation-list/

Hope this helps.