Flexible formula required to recreate list whilst excluding zero values

[jibbadiah]

Hi folks,

I'm using Excel 2002 and I'm trying to create a flexible formula to do the following:

I have 2 columns of data, one with a description and the other with a record count.
The count is a dynamic value based on a formula against a lot of data.
What I want to do is recreate another list of decriptions based on only those rows that contain a count > 0

SAMPLE DATA:
Desc Count
A 150
B 27
C 245
D 0
E 224
F 24
G 0
H 9
I 13
J 0
K 57

DESIRED RESULT:
desc
A
B
C
E
F
H
I
K

If another record count drops to zero it should be automatically picked up by the formula and removed from the list. The result is going to be used as a named range for a drop-down list, which will subsequently filter the records.
I could probably do this in vba but for the number of records that I'm going through it would be preferable to have a neat solution that automatically works from a formula. I've been looking at Index, address, vlookup, offset, etc... but I'm yet to come up with a nice combination. It can't be hard, but I'm starting to lose faith in my own ability to come up with the answer.

Any help much appreciated.

Regards,

James

[shg]

Welcome to the forum.

In order to avoid having to answer the same questions every day, we require that posters compose thread titles that make the forum usefully searchable. Your title doesn't advance that goal.

Please take a few minutes to read the Forum Rules about thread titles, and then edit yours to make it descriptive of your problem.

[shg]

How about autofilter, excluding zero?

[jibbadiah]

Autofilter isn't dynamic... so I would need to keep applying it after each change is made.
A formula like Offset would allow a combo-box to automatically update it's values if I could get it to work. I wouldn't need to add any vba to refresh/repaint the contents, etc.

I am actually using a form as a template, with loads of code behind it and the worksheets containing data and lookups will be hidden from the start. I don't want to have to interact with the worksheet manually at all in order for this to work. Screen Print Attached... the combo box in the top right is what I am referring to... as a user archives leads the combo box needs to update to remove any leads that no longer exist. I think that the best way will be a flexible formula, but I can always resort to vba and just delete lines if I have to. I may also be able to use a pivot table and hide zero results, but I'm not sure how easy this is either... would prefer a nice formula if I could get it to work.

I find it just as difficult to explain my problem as I do trying to work it out!!

[jibbadiah]

The following formula was close... but still not perfect... any takers?!

=IF(ROW(A1)>COUNT($B$1:$B$11)-COUNTIF($B$1:$B$11,"=0"),"",INDIRECT(ADDRESS(ROW(A1) + COUNTIF(INDIRECT("$B$1:$B" & ROW(A1) + COUNTIF($B$1:$B1,"=0")),"=0"),1)))