Drop down list

[hazzaska]

Hi All

I Am trying to create a dynamic drop down list from information contained in column A.

11:03

11:09


11:34

Column A is populated by the following formulas in cells A1 to A1000 =IFERROR(MID(B2,FIND("- ",B2)+2,5)+0,"") The cells are formatted as HH:MM

How can i pull out all the times into a list ignoring all blank cells, i am struggling to get this to work.

Thanks for any help

[BryceVBA]

try these instructions

highlight cell you want list to be in
Data tab>data validation
Allow: List
Source: A1 to A1000
make sure ignore blank is check marked
Ok

[Zer0Cool]

try these instructions

highlight cell you want list to be in
Data tab>data validation
Allow: List
Source: A1 to A1000
make sure ignore blank is check marked
Ok

The ignore blanks here will only ignore if someone enters a blank in the cell, ie: allow entering a value from the list or a blank in the data validation cell.

As for the list there is no straight forward way to ignore blanks in the source of a drop down. The assumption is that the target of your list includes all of the entries you want in the drop down and not entries you do not want. You may be able to using formulas/VBA generate another list without the blanks and base the dropdown on that, but that would be pretty complex.

[FlameRetired]

Another way.

There is a random list of times in column A of the attached. Some cells are "blank".

In column I is the list not containing blanks. The formula entered in cell I1 and filled down until you get blanks is an array formula. If you aren’t familiar with array-entered formulas array enter means the formula must be committed from edit mode by simultaneously pressing and holding down Ctrl and Shift while hitting Enter.

Please Login or Register  to view this content.

Then the attached uses D1 for the drop down. Data validation 'Allow:' is List. 'Source:' is

Please Login or Register  to view this content.

[FlameRetired]

Ooops!

I uploaded an *.xlsx file.

Here is a 2003 compatible version.

[hazzaska]

Ooops!

I uploaded an *.xlsx file.

Here is a 2003 compatible version.

That is spot on thanks for your help!

Much appreciated

H

[FlameRetired]

Good deal. You're welcome and thanks for the feedback.

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

[hazzaska]

Going on from this i have noticed if there are 2 times the same it gets confused, can i use some text at the start and still maintain my list?

Example

Tunstall 11:03


Tunstall 14:15


Seabridge 11:03


Tunstall 17.00

Can i have the venue names sorted in alphabetical order and the times too to stop 2 same times clashing.

As always thanks in advance

H