Google Groups no longer supports new Usenet posts or subscriptions. Historical content remains viewable.
Dismiss

sort column by cell color

35 views
Skip to first unread message

Russell Wright

unread,
Jun 14, 2002, 8:37:36 AM6/14/02
to
How can I sort a column based on cell format. I have a
long list, with cells of different colors. I would like to
have all of the white cells contiguous, and all the grey
cells contiguous, etc. I know how to sort based on data,
but not format. The help files are no help at all.

Thanks.

Jan Karel Pieterse

unread,
Jun 14, 2002, 9:42:02 AM6/14/02
to
Hi,

Define this name (Insert, Name, Define):

Name:
Colour
RefersTo:
=GET.CELL(63,OFFSET(INDIRECT("rc",FALSE),0,-1,1,1))

Now immediately to the *right* of the cells with colours,
insert a column and enter this function into the first row:

=Colour

So if column D has the coloured cells, enter the formula
in column E.

Copy down and sort on this column.

Regards,

Jan Karel Pieterse
Excel TA/MVP

>.
>

Russell Wright

unread,
Jun 14, 2002, 10:25:56 AM6/14/02
to
What do you mean, 'Copy down and sort on this column'?

>.
>

Peo Sjoblom

unread,
Jun 14, 2002, 11:31:40 AM6/14/02
to
When you put Jan Karel's formula (=color) in the first
adjacent cell of the colours column, grab the fillhandle
to copy down the formula (move mouse pointer to the lower
right corner of the cell with the formula, the pointer
changes from fat to thin cross, left click and hold while
dragging the mouse in a downward movement to copy the
formula)
The formula will return the colorindex for the colours
which probably would be 15 for grey and 0 for nothing if
you have default excel "colour", if white it is probably 2.
Now if you sort on the help column you'll get the same
colours contiguous..

Regards,

Peo Sjoblom

>.
>

Harlan Grove

unread,
Jun 14, 2002, 7:09:09 PM6/14/02
to
Jan Karel Pieterse <pieter...@compuserve.com> wrote...
...

>Define this name (Insert, Name, Define):
>
>Name:
>Colour
>RefersTo:
>=GET.CELL(63,OFFSET(INDIRECT("rc",FALSE),0,-1,1,1))
...

BE VERY CAREFUL WITH THIS!

Using XLM functions in defined names in Excel 2000 & prior versions is
dangerous. If you define such a name, use it in a cell formula, copy that
cell and paste it into a cell in another worksheet, you WILL throw an
unrecoverable application error. Meaning Excel WILL crash without giving you
any chance to save your work. Apparently this has been fixed in Excel
2002/XP.

Now for some criticism. This is a useless use of OFFSET.
INDIRECT("RC[-1]",False) would be more efficient.


Jan Karel Pieterse

unread,
Jun 17, 2002, 1:42:57 AM6/17/02
to
Hi Harlan,


> BE VERY CAREFUL WITH THIS!

Thanks, I keep forgetting to add that.

> Now for some criticism. This is a useless use of OFFSET.
> INDIRECT("RC[-1]",False) would be more efficient.

Hey, I did not know that one!

Thanks.

0 new messages