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"})

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"})

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"})

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"})

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

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"})