Creating a dynamic list

[JarrodA]

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?

 

[Biff]

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

 

[Jarrod A]

Thank you for this. I will have to play around with it a bit since I can get
it work in the example I showed here, but am having difficulties with the
actual worksheet I need it for.

The ColorIndex function does not update very well, I have noticed. If you
change the color of the cell, you have to "re-enter" the ColorIndex formula
by clicking to edit, then just hitting enter. *shrug*

 

[Biff]

Hi!

I contacted one of the contributors to that site and got
an explanantion of the ColorIndex function.

Basically, the function does not have a bug but that's
just the way it works due to the internal methods Excel
uses when coloring cells. So, with that in mind, I
probably would not use that function in any application
that I deemed to be important or critical.

They're going to include an explanation on the behavior of
that function on the site.

Perhaps a more reliable method for you to use would be to
use a conditional format to color the cells and then you
could extract the names to another list based on the
conditon used to set the format.

Biff