Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Need Help with Substitute Formulas, etc.
Hello,
I'm trying to find a formula that would allow me to enter any one of several categories into one cell and automatically determine the sub-category for that cell. I have tried using the "Substitute" function only to discover a major limitation which only lets me enter seven or eight categories. I need to be able to have this formula work with at least 15-20 categories. I have also, tried the "Lookup" function and the "If" function with similar problems. Below is a copy of the formulas that I have tried with no success. Can anyone help. Thanks in advance. =SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBST ITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(G6,"AFGHAN NEIGHBORHOOD","SECURITY"),"CIVILIAN CASUALTIES","SECURITY"),"COALITION ACTIVITY","SECURITY"),"CRIMINAL ACTIVITY","SECURITY"),"INSURGENT ACTIVITY","SECURITY"),"INTERNATIONAL EFFORTS","SECURITY"),"GOVERNMENT CORRUPTION","GOVERNANCE"),"GOVERNMENT EFFECTIVENESS","GOVERNANCE") =IF(G7="AFGHAN NEIGHBORHOOD","SECURITY",IF(G7="CIVILIAN CASUALTIES","SECURITY",IF(G7="GOVERNMENT CORRUPTION","GOVERNANCE",IF(G7="SOCIAL DEVELOPMENT","CROSS-CUTTING",IF(G7="HUMANITARIAN CRISIS","CROSS-CUTTING",IF(G7="ECONOMIC DEVELOPMENT","CROSS-CUTTING",IF(G7="GOVERNMENT EFFECTIVENESS","GOVERNANCE",IF(G7="NARCOTICS","GOV ERNANCE","MISC")))))))) =LOOKUP(G52,{"CIVILIAN CASUALTIES","ECONOMIC DEVELOPMENT","NARCOTICS"},{"SECURITY","CROSS CUTTING","GOVERNANCE"}) |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Need Help with Substitute Formulas, etc.
Correct me if I'm wrong, you you probably have multiple subcategories for a
given category. I'm thinking you may want something like a dependent list. http://www.contextures.com/xlDataVal13.html -- HTH, Barb Reinhardt "ISAF Media Analysis" wrote: Hello, I'm trying to find a formula that would allow me to enter any one of several categories into one cell and automatically determine the sub-category for that cell. I have tried using the "Substitute" function only to discover a major limitation which only lets me enter seven or eight categories. I need to be able to have this formula work with at least 15-20 categories. I have also, tried the "Lookup" function and the "If" function with similar problems. Below is a copy of the formulas that I have tried with no success. Can anyone help. Thanks in advance. =SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBST ITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(G6,"AFGHAN NEIGHBORHOOD","SECURITY"),"CIVILIAN CASUALTIES","SECURITY"),"COALITION ACTIVITY","SECURITY"),"CRIMINAL ACTIVITY","SECURITY"),"INSURGENT ACTIVITY","SECURITY"),"INTERNATIONAL EFFORTS","SECURITY"),"GOVERNMENT CORRUPTION","GOVERNANCE"),"GOVERNMENT EFFECTIVENESS","GOVERNANCE") =IF(G7="AFGHAN NEIGHBORHOOD","SECURITY",IF(G7="CIVILIAN CASUALTIES","SECURITY",IF(G7="GOVERNMENT CORRUPTION","GOVERNANCE",IF(G7="SOCIAL DEVELOPMENT","CROSS-CUTTING",IF(G7="HUMANITARIAN CRISIS","CROSS-CUTTING",IF(G7="ECONOMIC DEVELOPMENT","CROSS-CUTTING",IF(G7="GOVERNMENT EFFECTIVENESS","GOVERNANCE",IF(G7="NARCOTICS","GOV ERNANCE","MISC")))))))) =LOOKUP(G52,{"CIVILIAN CASUALTIES","ECONOMIC DEVELOPMENT","NARCOTICS"},{"SECURITY","CROSS CUTTING","GOVERNANCE"}) |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Need Help with Substitute Formulas, etc.
Set up a table in your sheet (eg in X1:Y15), made up like this:
AFGHAN NEIGHBORHOOD SECURITY CIVILIAN CASUALTIES SECURITY GOVERNMENT CORRUPTION GOVERNANCE SOCIAL DEVELOPMENT CROSS-CUTTING HUMANITARIAN CRISIS CROSS-CUTTING ECONOMIC DEVELOPMENT CROSS-CUTTING GOVERNMENT EFFECTIVENESS GOVERNANCE NARCOTICS GOVERNANCE and so on, and then you can use this formula: =VLOOKUP(G7,X$1:Y$15,2,0) Just add more items to the bottom of the table and adjust the table range accordingly. Hope this helps. Pete On Sep 1, 1:17*pm, ISAF Media Analysis wrote: Hello, I'm trying to find a formula that would allow me to enter any one of several categories into one cell and automatically determine the sub-category for that cell. *I have tried using the "Substitute" function only to discover a major limitation which only lets me enter seven or eight categories. *I need to be able to have this formula work with at least 15-20 categories. I have also, tried the "Lookup" function and the "If" function with similar problems. *Below is a copy of the formulas that I have tried with no success. *Can anyone help. *Thanks in advance. =SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBST ITUTE(SUBSTITUTE(SUBSTITU*TE(SUBSTITUTE(G6,"AFGHAN NEIGHBORHOOD","SECURITY"),"CIVILIAN CASUALTIES","SECURITY"),"COALITION ACTIVITY","SECURITY"),"CRIMINAL ACTIVITY","SECURITY"),"INSURGENT ACTIVITY","SECURITY"),"INTERNATIONAL EFFORTS","SECURITY"),"GOVERNMENT CORRUPTION","GOVERNANCE"),"GOVERNMENT EFFECTIVENESS","GOVERNANCE") =IF(G7="AFGHAN NEIGHBORHOOD","SECURITY",IF(G7="CIVILIAN CASUALTIES","SECURITY",IF(G7="GOVERNMENT CORRUPTION","GOVERNANCE",IF(G7="SOCIAL DEVELOPMENT","CROSS-CUTTING",IF(G7="HUMANITARIAN CRISIS","CROSS-CUTTING",IF(G7="ECONOMIC DEVELOPMENT","CROSS-CUTTING",IF(G7="GOVERNMENT EFFECTIVENESS","GOVERNANCE",IF(G7="NARCOTICS","GOV ERNANCE","MISC")))))))) =LOOKUP(G52,{"CIVILIAN CASUALTIES","ECONOMIC DEVELOPMENT","NARCOTICS"},{"SECURITY","CROSS CUTTING","GOVERNANCE"}) |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Need Help with Substitute Formulas, etc.
I am not sure if that would work, I would have to see an example of it.
Basically, if I input the word "Criminal Activity" into a cell I need the next cell to indicate "Security" If I input the term Government Corruption" into the same cell I need the next cell over "Governance." I have several categories like this. Cheers. "Barb Reinhardt" wrote: Correct me if I'm wrong, you you probably have multiple subcategories for a given category. I'm thinking you may want something like a dependent list. http://www.contextures.com/xlDataVal13.html -- HTH, Barb Reinhardt "ISAF Media Analysis" wrote: Hello, I'm trying to find a formula that would allow me to enter any one of several categories into one cell and automatically determine the sub-category for that cell. I have tried using the "Substitute" function only to discover a major limitation which only lets me enter seven or eight categories. I need to be able to have this formula work with at least 15-20 categories. I have also, tried the "Lookup" function and the "If" function with similar problems. Below is a copy of the formulas that I have tried with no success. Can anyone help. Thanks in advance. =SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBST ITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(G6,"AFGHAN NEIGHBORHOOD","SECURITY"),"CIVILIAN CASUALTIES","SECURITY"),"COALITION ACTIVITY","SECURITY"),"CRIMINAL ACTIVITY","SECURITY"),"INSURGENT ACTIVITY","SECURITY"),"INTERNATIONAL EFFORTS","SECURITY"),"GOVERNMENT CORRUPTION","GOVERNANCE"),"GOVERNMENT EFFECTIVENESS","GOVERNANCE") =IF(G7="AFGHAN NEIGHBORHOOD","SECURITY",IF(G7="CIVILIAN CASUALTIES","SECURITY",IF(G7="GOVERNMENT CORRUPTION","GOVERNANCE",IF(G7="SOCIAL DEVELOPMENT","CROSS-CUTTING",IF(G7="HUMANITARIAN CRISIS","CROSS-CUTTING",IF(G7="ECONOMIC DEVELOPMENT","CROSS-CUTTING",IF(G7="GOVERNMENT EFFECTIVENESS","GOVERNANCE",IF(G7="NARCOTICS","GOV ERNANCE","MISC")))))))) =LOOKUP(G52,{"CIVILIAN CASUALTIES","ECONOMIC DEVELOPMENT","NARCOTICS"},{"SECURITY","CROSS CUTTING","GOVERNANCE"}) |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Need Help with Substitute Formulas, etc.
Did you look at Debra's sample file?
ISAF Media Analysis wrote: I am not sure if that would work, I would have to see an example of it. Basically, if I input the word "Criminal Activity" into a cell I need the next cell to indicate "Security" If I input the term Government Corruption" into the same cell I need the next cell over "Governance." I have several categories like this. Cheers. "Barb Reinhardt" wrote: Correct me if I'm wrong, you you probably have multiple subcategories for a given category. I'm thinking you may want something like a dependent list. http://www.contextures.com/xlDataVal13.html -- HTH, Barb Reinhardt "ISAF Media Analysis" wrote: Hello, I'm trying to find a formula that would allow me to enter any one of several categories into one cell and automatically determine the sub-category for that cell. I have tried using the "Substitute" function only to discover a major limitation which only lets me enter seven or eight categories. I need to be able to have this formula work with at least 15-20 categories. I have also, tried the "Lookup" function and the "If" function with similar problems. Below is a copy of the formulas that I have tried with no success. Can anyone help. Thanks in advance. =SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBST ITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(G6,"AFGHAN NEIGHBORHOOD","SECURITY"),"CIVILIAN CASUALTIES","SECURITY"),"COALITION ACTIVITY","SECURITY"),"CRIMINAL ACTIVITY","SECURITY"),"INSURGENT ACTIVITY","SECURITY"),"INTERNATIONAL EFFORTS","SECURITY"),"GOVERNMENT CORRUPTION","GOVERNANCE"),"GOVERNMENT EFFECTIVENESS","GOVERNANCE") =IF(G7="AFGHAN NEIGHBORHOOD","SECURITY",IF(G7="CIVILIAN CASUALTIES","SECURITY",IF(G7="GOVERNMENT CORRUPTION","GOVERNANCE",IF(G7="SOCIAL DEVELOPMENT","CROSS-CUTTING",IF(G7="HUMANITARIAN CRISIS","CROSS-CUTTING",IF(G7="ECONOMIC DEVELOPMENT","CROSS-CUTTING",IF(G7="GOVERNMENT EFFECTIVENESS","GOVERNANCE",IF(G7="NARCOTICS","GOV ERNANCE","MISC")))))))) =LOOKUP(G52,{"CIVILIAN CASUALTIES","ECONOMIC DEVELOPMENT","NARCOTICS"},{"SECURITY","CROSS CUTTING","GOVERNANCE"}) -- Dave Peterson |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Need Help with Substitute Formulas, etc.
try vlookup
"ISAF Media Analysis" wrote in message ... Hello, I'm trying to find a formula that would allow me to enter any one of several categories into one cell and automatically determine the sub-category for that cell. I have tried using the "Substitute" function only to discover a major limitation which only lets me enter seven or eight categories. I need to be able to have this formula work with at least 15-20 categories. I have also, tried the "Lookup" function and the "If" function with similar problems. Below is a copy of the formulas that I have tried with no success. Can anyone help. Thanks in advance. =SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBST ITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(G6,"AFGHAN NEIGHBORHOOD","SECURITY"),"CIVILIAN CASUALTIES","SECURITY"),"COALITION ACTIVITY","SECURITY"),"CRIMINAL ACTIVITY","SECURITY"),"INSURGENT ACTIVITY","SECURITY"),"INTERNATIONAL EFFORTS","SECURITY"),"GOVERNMENT CORRUPTION","GOVERNANCE"),"GOVERNMENT EFFECTIVENESS","GOVERNANCE") =IF(G7="AFGHAN NEIGHBORHOOD","SECURITY",IF(G7="CIVILIAN CASUALTIES","SECURITY",IF(G7="GOVERNMENT CORRUPTION","GOVERNANCE",IF(G7="SOCIAL DEVELOPMENT","CROSS-CUTTING",IF(G7="HUMANITARIAN CRISIS","CROSS-CUTTING",IF(G7="ECONOMIC DEVELOPMENT","CROSS-CUTTING",IF(G7="GOVERNMENT EFFECTIVENESS","GOVERNANCE",IF(G7="NARCOTICS","GOV ERNANCE","MISC")))))))) =LOOKUP(G52,{"CIVILIAN CASUALTIES","ECONOMIC DEVELOPMENT","NARCOTICS"},{"SECURITY","CROSS CUTTING","GOVERNANCE"}) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How can I combine two formulas Left/Mid/Right + Substitute? | Excel Worksheet Functions | |||
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE.... max. limit 8 :( | Excel Discussion (Misc queries) | |||
Substitute | Excel Discussion (Misc queries) | |||
More than 7 IF? any substitute? | Excel Worksheet Functions | |||
Using &Chr$(39)& as substitute for ' in VBA | Excel Discussion (Misc queries) |