I have more than 3 different choices, ( ab, cd, ef, ghij, kl). They
are also aphanumeric, (.01 ab, 6 cd, .50 ef, 8 ghij),etc. The numbers
may range from .01 to 8.0. The letters will always remain the same. I
would like to change the color of all the ab entries to a specific
background color, regardless of the number range, and a different
selected color for the other alpha entries. If this is possible,
could I also obtain a sum total of all the numbers ony with the ab,
then with the cd,and each of the alphanumeric sets? How to get
started? Thanks.

Jim

is there always a space between the number and alpha parts?And is the number
always first?
--
paul

remove nospam for email addy!



" wrote:

I have more than 3 different choices, ( ab, cd, ef, ghij, kl). They
are also aphanumeric, (.01 ab, 6 cd, .50 ef, 8 ghij),etc. The numbers
may range from .01 to 8.0. The letters will always remain the same. I
would like to change the color of all the ab entries to a specific
background color, regardless of the number range, and a different
selected color for the other alpha entries. If this is possible,
could I also obtain a sum total of all the numbers ony with the ab,
then with the cd,and each of the alphanumeric sets? How to get
started? Thanks.

Jim

Yes, that is the way.

On Sat, 26 Aug 2006 00:02:01 -0700, paul
wrote:

is there always a space between the number and alpha parts?And is the number
always first?
--
paul

remove nospam for email addy!



" wrote:

I have more than 3 different choices, ( ab, cd, ef, ghij, kl). They
are also aphanumeric, (.01 ab, 6 cd, .50 ef, 8 ghij),etc. The numbers
may range from .01 to 8.0. The letters will always remain the same. I
would like to change the color of all the ab entries to a specific
background color, regardless of the number range, and a different
selected color for the other alpha entries. If this is possible,
could I also obtain a sum total of all the numbers ony with the ab,
then with the cd,and each of the alphanumeric sets? How to get
started? Thanks.

Jim

Is this even possible to accomplish with the manner in which the data
is entered? How would I get started with this?

Thanks,

Jim

On Sat, 26 Aug 2006 06:22:41 -0400, wrote:

Yes, that is the way.

On Sat, 26 Aug 2006 00:02:01 -0700, paul
wrote:

is there always a space between the number and alpha parts?And is the number
always first?
--
paul

remove nospam for email addy!



" wrote:

I have more than 3 different choices, ( ab, cd, ef, ghij, kl). They
are also aphanumeric, (.01 ab, 6 cd, .50 ef, 8 ghij),etc. The numbers
may range from .01 to 8.0. The letters will always remain the same. I
would like to change the color of all the ab entries to a specific
background color, regardless of the number range, and a different
selected color for the other alpha entries. If this is possible,
could I also obtain a sum total of all the numbers ony with the ab,
then with the cd,and each of the alphanumeric sets? How to get
started? Thanks.

Jim

there is a way but its hard to describe but i will try
I have the column of alphanumeric data in col f
so say cell f14 is your alphanumeric character
cell G14=VALUE(LEFT(F14,FIND(" ",F14)-1)) this finds the space and seperates
the number from the alpha part
in H 14 i have this formula
=IF(NOT(ISERROR(FIND("ab",F14,1))),"ab"
,IF(NOT(ISERROR(FIND("cd",F14,1))),"cd"
,IF(NOT(ISERROR(FIND("ef",F14,1))),"ef"
,IF(NOT(ISERROR(FIND("ghij",F14,1))),"ghif","kl")) ))
this examines the alpha numeric and splits out the alpha
conditional format cell ,formula is =H14="ab", choose the format you want
condition two formula is =H14<"ab",choose the format you want.
at the bottom of the G column type these formula
for ab =SUMIF($H$14:$H$22,"AB",$G$14:$G$22)
for cd =SUMIF($H$14:$H$22,"cd",$G$14:$G$22)
for ef =SUMIF($H$14:$H$22,"ef",$G$14:$G$22)
for ghij =SUMIF($H$14:$H$22,"ghij",$G$14:$G$22)
for kl =SUMIF($H$14:$H$22,"kl",$G$14:$G$22)
i have a sample sheet that i can email you.Email address below
make sure you put something about excelforum and alphanumeric in the subject
otherwise it will go straight to the trash
There may be a more elegant way but it works!

--
paul

remove nospam for email addy!



" wrote:

Is this even possible to accomplish with the manner in which the data
is entered? How would I get started with this?

Thanks,

Jim

On Sat, 26 Aug 2006 06:22:41 -0400, wrote:

Yes, that is the way.

On Sat, 26 Aug 2006 00:02:01 -0700, paul
wrote:

is there always a space between the number and alpha parts?And is the number
always first?
--
paul

remove nospam for email addy!



" wrote:

I have more than 3 different choices, ( ab, cd, ef, ghij, kl). They
are also aphanumeric, (.01 ab, 6 cd, .50 ef, 8 ghij),etc. The numbers
may range from .01 to 8.0. The letters will always remain the same. I
would like to change the color of all the ab entries to a specific
background color, regardless of the number range, and a different
selected color for the other alpha entries. If this is possible,
could I also obtain a sum total of all the numbers ony with the ab,
then with the cd,and each of the alphanumeric sets? How to get
started? Thanks.

Jim

There is a little more. The worksheet is two pages. The area that is
to change
color and is summed is made up of 4 cells. The cells are combined and
the data, ( .01 ab or 6 cd, etc) is input into the cell. It is those
4 cells that need to change color, depending on the input, as well as
to be summed separately. There are 365 groups of these 4 combined
cells.

I don't suppose one cell versus a combined group of cells may make a
hugh difference?

Thanks,

Jim

On Tue, 29 Aug 2006 03:20:02 -0700, paul
wrote:

there is a way but its hard to describe but i will try
I have the column of alphanumeric data in col f
so say cell f14 is your alphanumeric character
cell G14=VALUE(LEFT(F14,FIND(" ",F14)-1)) this finds the space and seperates
the number from the alpha part
in H 14 i have this formula
=IF(NOT(ISERROR(FIND("ab",F14,1))),"ab"
,IF(NOT(ISERROR(FIND("cd",F14,1))),"cd"
,IF(NOT(ISERROR(FIND("ef",F14,1))),"ef"
,IF(NOT(ISERROR(FIND("ghij",F14,1))),"ghif","kl") )))
this examines the alpha numeric and splits out the alpha
conditional format cell ,formula is =H14="ab", choose the format you want
condition two formula is =H14<"ab",choose the format you want.
at the bottom of the G column type these formula
for ab =SUMIF($H$14:$H$22,"AB",$G$14:$G$22)
for cd =SUMIF($H$14:$H$22,"cd",$G$14:$G$22)
for ef =SUMIF($H$14:$H$22,"ef",$G$14:$G$22)
for ghij =SUMIF($H$14:$H$22,"ghij",$G$14:$G$22)
for kl =SUMIF($H$14:$H$22,"kl",$G$14:$G$22)
i have a sample sheet that i can email you.Email address below
make sure you put something about excelforum and alphanumeric in the subject
otherwise it will go straight to the trash
There may be a more elegant way but it works!