I got to look over the excel file which you sent. It is very
interesting but it is not going to help. Perhaps my approach needs
some tweaking. But, on the spreadsheet, I will make these entries.
And it is in the same cell as the entry that I want to change color,
based on the entry alpha characters. I would also like to sum the
total of each unique alpha character to a predetermined cell for each
designation.


Thanks,

Jim



On Tue, 29 Aug 2006 07:48:34 -0400, wrote:

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!