| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Substitute
Help! :) I am using a workbook where in sheet 3 I'm looking at sheet 2 to see
if a value exists "x" in a column -- if it exists, I want to replace that value "x" with a value found in sheet 1 "4" -- I was able to create that formula as follows: =SUBSTITUTE(Master!E3,"x",'Curriculum Duration Estimates'!$D$5) However, I also want to look for value "i" in sheet 2 and if it exists, replace in sheet 3 value "i" with a value found in sheet 1 "1". I can't get that formula to work, I tried: =(SUBSTITUTE(Master!G3,"x",'Curriculum Duration Estimates'!$E$7))*OR(SUBSTITUTE(Master!G3,"i",'Cur riculum Duration Estimates'!$G$7)) Any ideas? |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Substitute
Thanks Glenn, that worked!
"Glenn" wrote: Trish wrote: Help! :) I am using a workbook where in sheet 3 I'm looking at sheet 2 to see if a value exists "x" in a column -- if it exists, I want to replace that value "x" with a value found in sheet 1 "4" -- I was able to create that formula as follows: =SUBSTITUTE(Master!E3,"x",'Curriculum Duration Estimates'!$D$5) However, I also want to look for value "i" in sheet 2 and if it exists, replace in sheet 3 value "i" with a value found in sheet 1 "1". I can't get that formula to work, I tried: =(SUBSTITUTE(Master!G3,"x",'Curriculum Duration Estimates'!$E$7))*OR(SUBSTITUTE(Master!G3,"i",'Cur riculum Duration Estimates'!$G$7)) Any ideas? =SUBSTITUTE(SUBSTITUTE(Master!E3,"i",'Curriculum Duration Estimates'!$D$5),"x",'Curriculum Duration Estimates'!$G$7) |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Substitute
Is it possible to do 3 criteria?
"Glenn" wrote: Trish wrote: Help! :) I am using a workbook where in sheet 3 I'm looking at sheet 2 to see if a value exists "x" in a column -- if it exists, I want to replace that value "x" with a value found in sheet 1 "4" -- I was able to create that formula as follows: =SUBSTITUTE(Master!E3,"x",'Curriculum Duration Estimates'!$D$5) However, I also want to look for value "i" in sheet 2 and if it exists, replace in sheet 3 value "i" with a value found in sheet 1 "1". I can't get that formula to work, I tried: =(SUBSTITUTE(Master!G3,"x",'Curriculum Duration Estimates'!$E$7))*OR(SUBSTITUTE(Master!G3,"i",'Cur riculum Duration Estimates'!$G$7)) Any ideas? =SUBSTITUTE(SUBSTITUTE(Master!E3,"i",'Curriculum Duration Estimates'!$D$5),"x",'Curriculum Duration Estimates'!$G$7) |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Substitute
is it possible to then sum the substitution results?
"Glenn" wrote: Trish wrote: Help! :) I am using a workbook where in sheet 3 I'm looking at sheet 2 to see if a value exists "x" in a column -- if it exists, I want to replace that value "x" with a value found in sheet 1 "4" -- I was able to create that formula as follows: =SUBSTITUTE(Master!E3,"x",'Curriculum Duration Estimates'!$D$5) However, I also want to look for value "i" in sheet 2 and if it exists, replace in sheet 3 value "i" with a value found in sheet 1 "1". I can't get that formula to work, I tried: =(SUBSTITUTE(Master!G3,"x",'Curriculum Duration Estimates'!$E$7))*OR(SUBSTITUTE(Master!G3,"i",'Cur riculum Duration Estimates'!$G$7)) Any ideas? =SUBSTITUTE(SUBSTITUTE(Master!E3,"i",'Curriculum Duration Estimates'!$D$5),"x",'Curriculum Duration Estimates'!$G$7) |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Substitute
Trish wrote:
Help! :) I am using a workbook where in sheet 3 I'm looking at sheet 2 to see if a value exists "x" in a column -- if it exists, I want to replace that value "x" with a value found in sheet 1 "4" -- I was able to create that formula as follows: =SUBSTITUTE(Master!E3,"x",'Curriculum Duration Estimates'!$D$5) However, I also want to look for value "i" in sheet 2 and if it exists, replace in sheet 3 value "i" with a value found in sheet 1 "1". I can't get that formula to work, I tried: =(SUBSTITUTE(Master!G3,"x",'Curriculum Duration Estimates'!$E$7))*OR(SUBSTITUTE(Master!G3,"i",'Cur riculum Duration Estimates'!$G$7)) Any ideas? =SUBSTITUTE(SUBSTITUTE(Master!E3,"i",'Curriculum Duration Estimates'!$D$5),"x",'Curriculum Duration Estimates'!$G$7) |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Substitute
Yes, you can nest additional SUBSTITUTE() functions. Just replace "Master!G3"
in the existing formula with another complete SUBSTITUTE() function. Trish wrote: Is it possible to do 3 criteria? "Glenn" wrote: Trish wrote: Help! :) I am using a workbook where in sheet 3 I'm looking at sheet 2 to see if a value exists "x" in a column -- if it exists, I want to replace that value "x" with a value found in sheet 1 "4" -- I was able to create that formula as follows: =SUBSTITUTE(Master!E3,"x",'Curriculum Duration Estimates'!$D$5) However, I also want to look for value "i" in sheet 2 and if it exists, replace in sheet 3 value "i" with a value found in sheet 1 "1". I can't get that formula to work, I tried: =(SUBSTITUTE(Master!G3,"x",'Curriculum Duration Estimates'!$E$7))*OR(SUBSTITUTE(Master!G3,"i",'Cur riculum Duration Estimates'!$G$7)) Any ideas? =SUBSTITUTE(SUBSTITUTE(Master!E3,"i",'Curriculum Duration Estimates'!$D$5),"x",'Curriculum Duration Estimates'!$G$7) |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Substitute
To change the result from text into a number, precede the calculation with "--":
=--SUBSTITUTE(SUBSTITUTE(Master!E3,"i",'Curriculum Duration Estimates'!$D$5),"x",'Curriculum Duration Estimates'!$G$7) Trish wrote: is it possible to then sum the substitution results? "Glenn" wrote: Trish wrote: Help! :) I am using a workbook where in sheet 3 I'm looking at sheet 2 to see if a value exists "x" in a column -- if it exists, I want to replace that value "x" with a value found in sheet 1 "4" -- I was able to create that formula as follows: =SUBSTITUTE(Master!E3,"x",'Curriculum Duration Estimates'!$D$5) However, I also want to look for value "i" in sheet 2 and if it exists, replace in sheet 3 value "i" with a value found in sheet 1 "1". I can't get that formula to work, I tried: =(SUBSTITUTE(Master!G3,"x",'Curriculum Duration Estimates'!$E$7))*OR(SUBSTITUTE(Master!G3,"i",'Cur riculum Duration Estimates'!$G$7)) Any ideas? =SUBSTITUTE(SUBSTITUTE(Master!E3,"i",'Curriculum Duration Estimates'!$D$5),"x",'Curriculum Duration Estimates'!$G$7) |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Substitute
Thanks for this answer and the other... still having trouble since I've got
some cells where there is no change because it has nothing in it so if I use "--" I get a value problem but I think I can fix that. "Glenn" wrote: To change the result from text into a number, precede the calculation with "--": =--SUBSTITUTE(SUBSTITUTE(Master!E3,"i",'Curriculum Duration Estimates'!$D$5),"x",'Curriculum Duration Estimates'!$G$7) Trish wrote: is it possible to then sum the substitution results? "Glenn" wrote: Trish wrote: Help! :) I am using a workbook where in sheet 3 I'm looking at sheet 2 to see if a value exists "x" in a column -- if it exists, I want to replace that value "x" with a value found in sheet 1 "4" -- I was able to create that formula as follows: =SUBSTITUTE(Master!E3,"x",'Curriculum Duration Estimates'!$D$5) However, I also want to look for value "i" in sheet 2 and if it exists, replace in sheet 3 value "i" with a value found in sheet 1 "1". I can't get that formula to work, I tried: =(SUBSTITUTE(Master!G3,"x",'Curriculum Duration Estimates'!$E$7))*OR(SUBSTITUTE(Master!G3,"i",'Cur riculum Duration Estimates'!$G$7)) Any ideas? =SUBSTITUTE(SUBSTITUTE(Master!E3,"i",'Curriculum Duration Estimates'!$D$5),"x",'Curriculum Duration Estimates'!$G$7) |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| =SUBSTITUTE(SUBSTITUTE(SUBSTITUTE.... max. limit 8 :( | Excel Discussion (Misc queries) | |||
| Substitute | Excel Discussion (Misc queries) | |||
| More than 7 IF? any substitute? | Excel Worksheet Functions | |||
| SUBSTITUTE | Excel Worksheet Functions | |||
| Using &Chr$(39)& as substitute for ' in VBA | Excel Discussion (Misc queries) | |||
Linear Mode
