|
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? |
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) |
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) |
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) |
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) |
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) |
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) |
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) |
| All times are GMT +1. The time now is 04:27 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com