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?

[sirknightly]

Jarrod,

Sounds like a bit of an odd problem, but I think you can do this without VB (wink, wink, since you're already using it in your ColourCounter function).

Follow the same directions on the website you list for sorting by color and create a "helper" column in column B. Then, select columns A and B and make a pivot table with your Name field (column A) as a row and a count of Name field (also column A, and only using it so the pivot will work) as the data field. Then place column B (whatever you decide to call this column with the color indices) in the Page field.

Use the page field to select the color you want to view. The pivot table will only display the names colored with the color you have selected.

Let me know if you need more info.

Knightly

Originally Posted by Jarrod A

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?