Remember Me?

#1
October 28th 04, 08:13 PM
 JarrodA Posts: n/a
Creating a dynamic list

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?

#2
October 29th 04, 05:42 AM
 Biff Posts: n/a

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?
.

#3
October 29th 04, 08:20 PM
 Jarrod A Posts: n/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" wrote:

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?
.

#4
October 30th 04, 04:01 AM
 Biff Posts: n/a

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

-----Original Message-----
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" wrote:

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?
.

.

 Posting Rules Smilies are On [IMG] code is On HTML code is OffTrackbacks are On Pingbacks are On Refbacks are On

 Similar Threads Thread Thread Starter Forum Replies Last Post Jarrod A Excel Worksheet Functions 1 November 18th 05 11:29 PM Susan Excel Discussion (Misc queries) 2 January 14th 05 09:21 PM B Schwarz Excel Discussion (Misc queries) 4 January 7th 05 01:48 PM Fysh Charts and Charting in Excel 9 December 15th 04 02:52 AM Jad Excel Worksheet Functions 1 October 29th 04 06:00 AM

All times are GMT +1. The time now is 06:43 PM.