I have a problem that I need to get resolved as soon as possible and need
help. I have created a tracking sheet for some stock investments I have.
The web site I use for information is a pay site that uses alpha and numeric
characters to show strength and weakness. I have no problem with the number
part of this, but the alpha part is driving me crazy. I need to have the
alpha characters in the following sequence of order. A+, A, A-, B+, B, B-,
C+, C, C-, etc...., but when I use the same =AND formula that I use for the
numbers the sequence comes out like this. A+, A-, A, B+, B-, B, etc.... I
am shading the cell red if the number or letter decreases, green if it
increases and yellow if it stays the same. Since A is suppose to follow A+
and be higher in ranking than A- it should show green if A- was in the cell
above it in the column and it doesn't it shows red as if it were a worse
rating. Can someone help me figure this out???? Thanks in advance for your
time.

Setup a table on another sheet that assigns a numerical score to each letter
combination

A+ 99
A 98
A- 97
B+ 96
B 95
B- 94
C+ 93
C 92
C- 91

then in you conditional formula instead of referencing the cell with a
letter rating directly, use vlookup to change it to a value

assume D20 and D21 have letter ratings

=If(vlookup(D21,Table,2,False)Vlookup(D20,Table,2 ,False),"Up","Down")

as an example usage. Hopefully you can incorporate that into your formulas.

--
Regards,
Tom Ogilvy

"craftwoodman" wrote in message
...
I have a problem that I need to get resolved as soon as possible and need
help. I have created a tracking sheet for some stock investments I have.
The web site I use for information is a pay site that uses alpha and
numeric
characters to show strength and weakness. I have no problem with the
number
part of this, but the alpha part is driving me crazy. I need to have the
alpha characters in the following sequence of order. A+, A, A-, B+, B, B-,
C+, C, C-, etc...., but when I use the same =AND formula that I use for
the
numbers the sequence comes out like this. A+, A-, A, B+, B-, B, etc....
I
am shading the cell red if the number or letter decreases, green if it
increases and yellow if it stays the same. Since A is suppose to follow
A+
and be higher in ranking than A- it should show green if A- was in the
cell
above it in the column and it doesn't it shows red as if it were a worse
rating. Can someone help me figure this out???? Thanks in advance for
your
time.

You could try creating a custom list to sort by. Click on Tools/Options and
the Custom List tab. Enter a new list in the sequence you want and then when
you sort select the custom list.
Gary Rowe

"craftwoodman" wrote:

I have a problem that I need to get resolved as soon as possible and need
help. I have created a tracking sheet for some stock investments I have.
The web site I use for information is a pay site that uses alpha and numeric
characters to show strength and weakness. I have no problem with the number
part of this, but the alpha part is driving me crazy. I need to have the
alpha characters in the following sequence of order. A+, A, A-, B+, B, B-,
C+, C, C-, etc...., but when I use the same =AND formula that I use for the
numbers the sequence comes out like this. A+, A-, A, B+, B-, B, etc.... I
am shading the cell red if the number or letter decreases, green if it
increases and yellow if it stays the same. Since A is suppose to follow A+
and be higher in ranking than A- it should show green if A- was in the cell
above it in the column and it doesn't it shows red as if it were a worse
rating. Can someone help me figure this out???? Thanks in advance for your
time.

Gary,
Thanks for the tip, but I don't need to sort. I tried this and it doesn't
do what I need. What I am trying to do is color code a cell based on what is
entered into it. For example: if cell A1 has an A- in it and I enter an A in
cell A2, the color of the cell should change to green since the letter went
up in value. What is happening is excel recognizes the following sequential
order: A+, A-, A. So in this scenario cell A2 would have actually turned red
since excel's order is different. I need to create or change something so
that cell A2 turns green. I am trying to create a table, but am not having
much luck at it.

"Gary Rowe" wrote:

You could try creating a custom list to sort by. Click on Tools/Options and
the Custom List tab. Enter a new list in the sequence you want and then when
you sort select the custom list.
Gary Rowe

"craftwoodman" wrote:

I have a problem that I need to get resolved as soon as possible and need
help. I have created a tracking sheet for some stock investments I have.
The web site I use for information is a pay site that uses alpha and numeric
characters to show strength and weakness. I have no problem with the number
part of this, but the alpha part is driving me crazy. I need to have the
alpha characters in the following sequence of order. A+, A, A-, B+, B, B-,
C+, C, C-, etc...., but when I use the same =AND formula that I use for the
numbers the sequence comes out like this. A+, A-, A, B+, B-, B, etc.... I
am shading the cell red if the number or letter decreases, green if it
increases and yellow if it stays the same. Since A is suppose to follow A+
and be higher in ranking than A- it should show green if A- was in the cell
above it in the column and it doesn't it shows red as if it were a worse
rating. Can someone help me figure this out???? Thanks in advance for your
time.

Tom,
Thanks for the tip, but I consider myself a little more than a novice when
it comes to excel. I have tried to create a table on a seperate worksheet,
but I must be doing something wrong, because when I do this and do the
conditional formatting, nothing happens. I must be doing something wrong.

"Tom Ogilvy" wrote:

Setup a table on another sheet that assigns a numerical score to each letter
combination

A+ 99
A 98
A- 97
B+ 96
B 95
B- 94
C+ 93
C 92
C- 91

then in you conditional formula instead of referencing the cell with a
letter rating directly, use vlookup to change it to a value

assume D20 and D21 have letter ratings

=If(vlookup(D21,Table,2,False)Vlookup(D20,Table,2 ,False),"Up","Down")

as an example usage. Hopefully you can incorporate that into your formulas.

--
Regards,
Tom Ogilvy

"craftwoodman" wrote in message
...
I have a problem that I need to get resolved as soon as possible and need
help. I have created a tracking sheet for some stock investments I have.
The web site I use for information is a pay site that uses alpha and
numeric
characters to show strength and weakness. I have no problem with the
number
part of this, but the alpha part is driving me crazy. I need to have the
alpha characters in the following sequence of order. A+, A, A-, B+, B, B-,
C+, C, C-, etc...., but when I use the same =AND formula that I use for
the
numbers the sequence comes out like this. A+, A-, A, B+, B-, B, etc....
I
am shading the cell red if the number or letter decreases, green if it
increases and yellow if it stays the same. Since A is suppose to follow
A+
and be higher in ranking than A- it should show green if A- was in the
cell
above it in the column and it doesn't it shows red as if it were a worse
rating. Can someone help me figure this out???? Thanks in advance for
your
time.

Let's try this:

InY1 to Y9, enter this:
A+,A,A-,B+,B,B-,C+,C+C-

In Z1 to Z9, enter this:
9,8,7,6,5,4,3,2,1

This sets up your datalist.

Now, start by clicking in A2.
Then <Format <ConditionalFormat
Change "CellValueIs" to "Formula Is",
And enter this formula:

=VLOOKUP(A2,$Y$1:$Z$9,2,0)VLOOKUP(A1,$Y$1:$Z$9,2, 0)
Set the format to Green and Bold, then <OK,

Then click the ADD button to enable the entry of condition #2
Again, change "CellValueIs" to "Formula Is",
And enter this formula:

=VLOOKUP(A2,$Y$1:$Z$9,2,0)=VLOOKUP(A1,$Y$1:$Z$9,2, 0)
*NOTE* - same as first formula except "" changed to "="
Set the format to Gold and Bold, then <OK,

Then click the ADD button again to enable the entry of condition #3
Again, change "CellValueIs" to "Formula Is",
And enter this same formula:

=VLOOKUP(A2,$Y$1:$Z$9,2,0)<VLOOKUP(A1,$Y$1:$Z$9,2, 0)
With the CHANGE here being from "=" to "<"
Set the format to Red and Bold, then <OK, <OK.

You've set the format for A2.

Now, click in A2, then click on the "FormatPainter" icon on the toolbar
(yellow paintbrush),
Click in A3, and drag down to copy as needed, to set the same format for the
rest of the cells in Column A.
--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"craftwoodman" wrote in message
...
Gary,
Thanks for the tip, but I don't need to sort. I tried this and it doesn't
do what I need. What I am trying to do is color code a cell based on what
is
entered into it. For example: if cell A1 has an A- in it and I enter an A
in
cell A2, the color of the cell should change to green since the letter
went
up in value. What is happening is excel recognizes the following
sequential
order: A+, A-, A. So in this scenario cell A2 would have actually turned
red
since excel's order is different. I need to create or change something so
that cell A2 turns green. I am trying to create a table, but am not
having
much luck at it.

"Gary Rowe" wrote:

You could try creating a custom list to sort by. Click on Tools/Options
and
the Custom List tab. Enter a new list in the sequence you want and then
when
you sort select the custom list.
Gary Rowe

"craftwoodman" wrote:

I have a problem that I need to get resolved as soon as possible and
need
help. I have created a tracking sheet for some stock investments I
have.
The web site I use for information is a pay site that uses alpha and
numeric
characters to show strength and weakness. I have no problem with the
number
part of this, but the alpha part is driving me crazy. I need to have
the
alpha characters in the following sequence of order. A+, A, A-, B+, B,
B-,
C+, C, C-, etc...., but when I use the same =AND formula that I use
for the
numbers the sequence comes out like this. A+, A-, A, B+, B-, B,
etc.... I
am shading the cell red if the number or letter decreases, green if it
increases and yellow if it stays the same. Since A is suppose to
follow A+
and be higher in ranking than A- it should show green if A- was in the
cell
above it in the column and it doesn't it shows red as if it were a
worse
rating. Can someone help me figure this out???? Thanks in advance
for your
time.

Ragdyer,
I want to thank you for this help. IT WORKED. You can't believe how much
time you are going to save me. I can't say thanks enough. Have a great new
year.

"Ragdyer" wrote:

Let's try this:

InY1 to Y9, enter this:
A+,A,A-,B+,B,B-,C+,C+C-

In Z1 to Z9, enter this:
9,8,7,6,5,4,3,2,1

This sets up your datalist.

Now, start by clicking in A2.
Then <Format <ConditionalFormat
Change "CellValueIs" to "Formula Is",
And enter this formula:

=VLOOKUP(A2,$Y$1:$Z$9,2,0)VLOOKUP(A1,$Y$1:$Z$9,2, 0)
Set the format to Green and Bold, then <OK,

Then click the ADD button to enable the entry of condition #2
Again, change "CellValueIs" to "Formula Is",
And enter this formula:

=VLOOKUP(A2,$Y$1:$Z$9,2,0)=VLOOKUP(A1,$Y$1:$Z$9,2, 0)
*NOTE* - same as first formula except "" changed to "="
Set the format to Gold and Bold, then <OK,

Then click the ADD button again to enable the entry of condition #3
Again, change "CellValueIs" to "Formula Is",
And enter this same formula:

=VLOOKUP(A2,$Y$1:$Z$9,2,0)<VLOOKUP(A1,$Y$1:$Z$9,2, 0)
With the CHANGE here being from "=" to "<"
Set the format to Red and Bold, then <OK, <OK.

You've set the format for A2.

Now, click in A2, then click on the "FormatPainter" icon on the toolbar
(yellow paintbrush),
Click in A3, and drag down to copy as needed, to set the same format for the
rest of the cells in Column A.
--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"craftwoodman" wrote in message
...
Gary,
Thanks for the tip, but I don't need to sort. I tried this and it doesn't
do what I need. What I am trying to do is color code a cell based on what
is
entered into it. For example: if cell A1 has an A- in it and I enter an A
in
cell A2, the color of the cell should change to green since the letter
went
up in value. What is happening is excel recognizes the following
sequential
order: A+, A-, A. So in this scenario cell A2 would have actually turned
red
since excel's order is different. I need to create or change something so
that cell A2 turns green. I am trying to create a table, but am not
having
much luck at it.

"Gary Rowe" wrote:

You could try creating a custom list to sort by. Click on Tools/Options
and
the Custom List tab. Enter a new list in the sequence you want and then
when
you sort select the custom list.
Gary Rowe

"craftwoodman" wrote:

I have a problem that I need to get resolved as soon as possible and
need
help. I have created a tracking sheet for some stock investments I
have.
The web site I use for information is a pay site that uses alpha and
numeric
characters to show strength and weakness. I have no problem with the
number
part of this, but the alpha part is driving me crazy. I need to have
the
alpha characters in the following sequence of order. A+, A, A-, B+, B,
B-,
C+, C, C-, etc...., but when I use the same =AND formula that I use
for the
numbers the sequence comes out like this. A+, A-, A, B+, B-, B,
etc.... I
am shading the cell red if the number or letter decreases, green if it
increases and yellow if it stays the same. Since A is suppose to
follow A+
and be higher in ranking than A- it should show green if A- was in the
cell
above it in the column and it doesn't it shows red as if it were a
worse
rating. Can someone help me figure this out???? Thanks in advance
for your
time.

Thanks for the feed-back.
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"craftwoodman" wrote in message
...
Ragdyer,
I want to thank you for this help. IT WORKED. You can't believe how much
time you are going to save me. I can't say thanks enough. Have a great
new
year.

"Ragdyer" wrote:

Let's try this:

InY1 to Y9, enter this:
A+,A,A-,B+,B,B-,C+,C+C-

In Z1 to Z9, enter this:
9,8,7,6,5,4,3,2,1

This sets up your datalist.

Now, start by clicking in A2.
Then <Format <ConditionalFormat
Change "CellValueIs" to "Formula Is",
And enter this formula:

=VLOOKUP(A2,$Y$1:$Z$9,2,0)VLOOKUP(A1,$Y$1:$Z$9,2, 0)
Set the format to Green and Bold, then <OK,

Then click the ADD button to enable the entry of condition #2
Again, change "CellValueIs" to "Formula Is",
And enter this formula:

=VLOOKUP(A2,$Y$1:$Z$9,2,0)=VLOOKUP(A1,$Y$1:$Z$9,2, 0)
*NOTE* - same as first formula except "" changed to "="
Set the format to Gold and Bold, then <OK,

Then click the ADD button again to enable the entry of condition #3
Again, change "CellValueIs" to "Formula Is",
And enter this same formula:

=VLOOKUP(A2,$Y$1:$Z$9,2,0)<VLOOKUP(A1,$Y$1:$Z$9,2, 0)
With the CHANGE here being from "=" to "<"
Set the format to Red and Bold, then <OK, <OK.

You've set the format for A2.

Now, click in A2, then click on the "FormatPainter" icon on the toolbar
(yellow paintbrush),
Click in A3, and drag down to copy as needed, to set the same format for
the
rest of the cells in Column A.
--
HTH,

RD


--------------------------------------------------------------------------
-
Please keep all correspondence within the NewsGroup, so all may benefit
!

--------------------------------------------------------------------------
-
"craftwoodman" wrote in message
...
Gary,
Thanks for the tip, but I don't need to sort. I tried this and it
doesn't
do what I need. What I am trying to do is color code a cell based on
what
is
entered into it. For example: if cell A1 has an A- in it and I enter
an A
in
cell A2, the color of the cell should change to green since the letter
went
up in value. What is happening is excel recognizes the following
sequential
order: A+, A-, A. So in this scenario cell A2 would have actually
turned
red
since excel's order is different. I need to create or change
something so
that cell A2 turns green. I am trying to create a table, but am not
having
much luck at it.

"Gary Rowe" wrote:

You could try creating a custom list to sort by. Click on
Tools/Options
and
the Custom List tab. Enter a new list in the sequence you want and
then
when
you sort select the custom list.
Gary Rowe

"craftwoodman" wrote:

I have a problem that I need to get resolved as soon as possible
and
need
help. I have created a tracking sheet for some stock investments
I
have.
The web site I use for information is a pay site that uses alpha
and
numeric
characters to show strength and weakness. I have no problem with
the
number
part of this, but the alpha part is driving me crazy. I need to
have
the
alpha characters in the following sequence of order. A+, A, A-,
B+, B,
B-,
C+, C, C-, etc...., but when I use the same =AND formula that I
use
for the
numbers the sequence comes out like this. A+, A-, A, B+, B-, B,
etc.... I
am shading the cell red if the number or letter decreases, green
if it
increases and yellow if it stays the same. Since A is suppose to
follow A+
and be higher in ranking than A- it should show green if A- was in
the
cell
above it in the column and it doesn't it shows red as if it were a
worse
rating. Can someone help me figure this out???? Thanks in
advance
for your
time.