Create dynamic dropdown from range including blanks

Hi there,

I have a spreadsheet that includes a column of stages of development. This
spreadsheet can be used for tracking issues for any of 9 stages. I'd like
the user to be able to select a dropdown at the top of the page that shows a
summary of current issues (anything where the stage column equals the
selected value from the dropdown) and then 'other' issues that are outside
this value.

I'd also like to build the values in the dropdown based on a range of cells
that could contain blanks. For instance if the issues list contains 5
issues, I might have the defined range set to 300 rows. If the only two
values in the range are Development and Testing, I want the dropdown to
ignore the blanks and only show these two values. Any ideas on how to
accomplish this? I'd like to eliminate the need for an extra hidden sheet if
possible, as well as the need to define all nine phases in a list (so if
future expansion added another phase, the code wouldn't need to change).
Please let me know your thoughts, and thanks in advance.

Best Regards,
Mike Mick

If this is set up like a database (and it sounds like it is), then Perhaps
what you want is just to apply an autofilter to the data. The dropdown in
the stage column would show the unique list of stages. Select a stage, then
when you go to the issues column, only issues for that stage would show in
the dropdown. If you select all your data including headers before applying
the filter, then your filter will include data event when separated by blank
rows.

While this isn't exaclty what you described, it is very close conceptually
and would be a lot cleaner than writing tons of code to essentially duplicte
it with slight modifications.

If you want to continue along the path you have stated, then see Debra
Dalgleish's pages for some ideas:

http://www.contextures.com/tiptech.html



--
Regards,
Tom Ogilvy



"Mike Mick" wrote:

> Hi there,
>
> I have a spreadsheet that includes a column of stages of development. This
> spreadsheet can be used for tracking issues for any of 9 stages. I'd like
> the user to be able to select a dropdown at the top of the page that shows a
> summary of current issues (anything where the stage column equals the
> selected value from the dropdown) and then 'other' issues that are outside
> this value.
>
> I'd also like to build the values in the dropdown based on a range of cells
> that could contain blanks. For instance if the issues list contains 5
> issues, I might have the defined range set to 300 rows. If the only two
> values in the range are Development and Testing, I want the dropdown to
> ignore the blanks and only show these two values. Any ideas on how to
> accomplish this? I'd like to eliminate the need for an extra hidden sheet if
> possible, as well as the need to define all nine phases in a list (so if
> future expansion added another phase, the code wouldn't need to change).
> Please let me know your thoughts, and thanks in advance.
>
> Best Regards,
> Mike Mick