Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
alphanumeric range
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
alphanumeric range
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
alphanumeric range
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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
alphanumeric range
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 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
alphanumeric range
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! |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
alphanumeric range
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! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Duplicate Range Names by worksheet | Excel Discussion (Misc queries) | |||
Help with using range names in sum function | Excel Worksheet Functions | |||
How to Select a relative range with Using "Find" and Offset() | Excel Discussion (Misc queries) | |||
Can a formula check for a certain value in a range? | Excel Discussion (Misc queries) | |||
can a formula check for a certain value in a range? | Excel Discussion (Misc queries) |