Hi!

This formula will work if you know what the color index of
the cells is. Entered as an array with the key combo of
CTRL,SHIFT,ENTER:

=INDEX(A$2:A$6,SMALL(IF(colorindex(A$2:A$6)=35,ROW
(A$1:A$5)),ROW(1:1)))

In my example I used cells with a fill color of light
green. You would copy the formula down until you get #NUM!
errors or you can use error trapping to return a blank
cell. If you choose to use error trapping then you would
have to copy the formula to enough cells to cover the
highest possible number of names that might meet the
criteria.

Note: While testing this colorindex UDF I found that it
has a bug. If you have a cell that contains a fill color
and use the formula =COLORINDEX() and then clear that
cells fill color, the COLORINDEX function still returns
the index number of the fill color even though it was
cleared. It also doen't seem to update as the cells fill
color changes.

Biff

-----Original Message-----
I have a list of names on a worksheet. Each name can be
in one of two
statuses, indicated on the sheet by cell color. I am
keeping track of the
total number of each type by using the ColorIndex
function I found at
http://www.xldynamic.com/source/xld.ColourCounter.html.

However, I also want to be able to create a new list that
contains ONLY the
names of one particular status and will change as the
original list changes.
I would like this list to be formatted "nicely". In
other words, I know I
could just create a whole bunch of lines like =IF
(ColorIndex(D6)="6",D6,""),
but that would leave a bunch of blank lines.

Example:

A B C D E
1 Name Total1 Total2

2 John 3 2

3 Sarah

4 Jim

5 Steven

6 Bill

Assuming that A2, A4 and A6 were all the same cell color
(shown under
Total1), I would like to create this a list in F:

A B C D E F
1 Name Total1 Total2 John

2 John 3 2 Jim

3 Sarah Bill

4 Jim

5 Steven

6 Bill

As the status (color) of the fields in A changed, the
list in F would
change.

I am assuming that VB would be necessary for this...any
suggestions?
.