|
| If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|||||||
|
|
Thread Tools | Display Modes |
|
#1
March 9th, 2007, 03:37 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUBSTITUTE Function - Nesting Limitation
Maybe Subsitute is the wrong function.. if it is, let me know of a cleaner
easier way to do this. Simply, I have a list of 10 Categories, each relating to an individual. The issues are all listed in a drop down box in Column B. I want Colmun F to list the individual automatically. Lets say the Categories a Cat1, Cat2, Cat3.... And the related people a Per1, Per2, Per3... When I put "Cat1" is cell B4, I want "Per1" to appear in F4 The formula I wrote for this is F4 = SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(B4,"Cat1","Per1") ,"Cat2","Per2"),"Cat3","Per3).... This works. The problem is that with 10 Categories, and the limitation of only nesting 7 functions... I can't get it to work for the whole group. Can anyone suggest a workaround.. or a different function or way to do this? Thanks.. I really appreciate it. Dave 
|
|
#2
March 9th, 2007, 04:27 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
SUBSTITUTE Function - Nesting Limitation
Take a look VLOOKUP function in help menu
"D Bagatelle" wrote: Maybe Subsitute is the wrong function.. if it is, let me know of a cleaner easier way to do this. Simply, I have a list of 10 Categories, each relating to an individual. The issues are all listed in a drop down box in Column B. I want Colmun F to list the individual automatically. Lets say the Categories a Cat1, Cat2, Cat3.... And the related people a Per1, Per2, Per3... When I put "Cat1" is cell B4, I want "Per1" to appear in F4 The formula I wrote for this is F4 = SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(B4,"Cat1","Per1") ,"Cat2","Per2"),"Cat3","Per3).... This works. The problem is that with 10 Categories, and the limitation of only nesting 7 functions... I can't get it to work for the whole group. Can anyone suggest a workaround.. or a different function or way to do this? Thanks.. I really appreciate it. Dave
|
|
#3
March 10th, 2007, 02:25 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
SUBSTITUTE Function - Nesting Limitation
I think reality is going to prove that the VLOOKUP() solution is really the
one to use, but if the situation were exactly as you've described, you could have used =SUBSTITUTE(B4,"Cat","Per") "D Bagatelle" wrote: Maybe Subsitute is the wrong function.. if it is, let me know of a cleaner easier way to do this. Simply, I have a list of 10 Categories, each relating to an individual. The issues are all listed in a drop down box in Column B. I want Colmun F to list the individual automatically. Lets say the Categories a Cat1, Cat2, Cat3.... And the related people a Per1, Per2, Per3... When I put "Cat1" is cell B4, I want "Per1" to appear in F4 The formula I wrote for this is F4 = SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(B4,"Cat1","Per1") ,"Cat2","Per2"),"Cat3","Per3).... This works. The problem is that with 10 Categories, and the limitation of only nesting 7 functions... I can't get it to work for the whole group. Can anyone suggest a workaround.. or a different function or way to do this? Thanks.. I really appreciate it. Dave
|
| Thread Tools | |
| Display Modes | |
|
|
Linear Mode
