Results 1 to 7 of 7
Thread: substitute formula (excel xp)

20021209, 17:20 #1
 Join Date
 Jan 2001
 Location
 Illinois
 Posts
 552
 Thanks
 0
 Thanked 1 Time in 1 Post
substitute formula (excel xp)
I have this formula in A20
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(IF(ISNONTEXT((F2 1)),(F21),(F21)&IF(NOW(),"",)),"I",""),"J",""),"K" ,"")
When F21 is blank the formula returns a blank. My problem is that i link to this cell, A20, from another worksheet and it gives me a #NA error instead of a blank cell. I don't know why. I've tried (ISNUMBER ... also instead of ISNONTEXT but both give the same result.
thanks

20021209, 18:02 #2
 Join Date
 Jan 2001
 Location
 Redcliff, Alberta, Canada
 Posts
 4,066
 Thanks
 2
 Thanked 5 Times in 5 Posts
Re: substitute formula (excel xp)
How about ISERROR?
Cheers[b]Catharine Richardson (WebGenii)
WebGenii Home Page
Moderator: Spreadsheets, Other MS Apps, Presentation Apps, Visual Basic for Apps, Windows Mobile

20021209, 18:57 #3
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
Re: substitute formula (excel xp)
If cell F21 is NOT text, the result is A20. If A20 is blank it gives a null string, if it is the number 10, the result of the function is the number 10. If A20 is NA#, the result will be NA#.
You need another IF to check for errors:
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(IF(ISNONTEXT((F2 1)),IF(ISERROR(F21),"",F21),(F21)&IF(NOW(),"",))," I",""),"J",""),"K","")
Steve

20021212, 14:53 #4
 Join Date
 Jan 2001
 Location
 Illinois
 Posts
 552
 Thanks
 0
 Thanked 1 Time in 1 Post
Re: substitute formula (excel xp)
This pesty #N/A is back to haunt me.
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(IF(ISNONTEXT((F2 1)),IF(ISERROR(F21),"",F21),(F21)&IF(NOW(),"",))," I",""),"J",""),"K","") this formula is in AF21. F21 is blank.
i then refer to AF21 on another worksheet which is giving me the #N/A. When i change AF21 to blank, i just delete the formula, then the reference on the other worksheet becomes blank and the #N/A disappear.
I don't know what else i can do to get this to work.
This is the cell, f1038, that is showing the #N/A
=IF(ISNUMBER(onedown(E1038)),onedown(E1038),IF(ISB LANK(onedown(E1038)),"",LOOKUP(LEFT(onedown(E1038) ,1),LimMoMac.xls!BBW)&IF(LEN(onedown(E1038))>1,CHA R(44)&LOOKUP(MID(onedown(E1038),2,1),LimMoMac.xls! BBW),"")&IF(LEN(onedown(E1038))>2,CHAR(44)&LOOKUP( MID(onedown(E1038),3,1),LimMoMac.xls!BBW),"")&IF(L EN(onedown(E1038))>3,CHAR(44)&LOOKUP(MID(onedown(E 1038),4,1),LimMoMac.xls!BBW),"")&IF(LEN(onedown(E1 038))>4,CHAR(44)&LOOKUP(MID(onedown(E1038),5,1),Li mMoMac.xls!BBW),"")&IF(LEN(onedown(E1038))>5,CHAR( 44)&LOOKUP(MID(onedown(E1038),6,1),LimMoMac.xls!BB W),"")&IF(LEN(onedown(E1038))>6,CHAR(44)&LOOKUP(MI D(onedown(E1038),7,1),LimMoMac.xls!BBW),"")&IF(LEN (onedown(E1038))>7,CHAR(44)&LOOKUP(MID(onedown(E10 38),8,1),LimMoMac.xls!BBW),"")&IF(LEN(onedown(E103 8))>8,CHAR(44)&LOOKUP(MID(onedown(E1038),9,1),LimM oMac.xls!BBW),"")&IF(LEN(onedown(E1038))>9,CHAR(44 )&LOOKUP(MID(onedown(E1038),10,1),LimMoMac.xls!BBW ),"")&IF(LEN(onedown(E1038))>10,CHAR(44)&LOOKUP(MI D(onedown(E1038),11,1),LimMoMac.xls!BBW),IF(NOW()< >0,"",""))))
the onedown function looks at one column to the left's formula and goes one down from that worksheet and cell.
Public Function OneDown(oCell As Range) As Variant
Dim strSName As String
Dim strCAdd As String
strSName = Mid(oCell.Formula, 2, InStr(oCell.Formula, "!")  2)
strCAdd = Right(oCell.Formula, Len(oCell.Formula)  InStr(oCell.Formula, "!"))
OneDown = Worksheets(strSName).Range(strCAdd).Offset(1, 0).Value
End Function
Thank you for your help.

20021213, 00:12 #5
 Join Date
 May 2002
 Location
 Canberra, Australian Capital Territory, Australia
 Posts
 5,055
 Thanks
 2
 Thanked 417 Times in 346 Posts
Re: substitute formula (excel xp)
Hi jha,
Let's try simplifying things a bit.
Firstly, change:
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(IF(ISNONTEXT((F2 1)),IF(ISERROR(F21),"",F21),(F21)&IF(NOW(),"",))," I",""),"J",""),"K","")
to
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(F21,"I",""),"J", ""),"K","")
That should result in F21 being returned unchanged if it has a number, is empty, or doesn't have I, J or K, and with I, J and K omitted otherwise. I haven't bothered testing for a number in this statement, since your long formula does that and looks like it's intended to retain numbers. If it's not, let me know.
Next, replace
IF(NOW()<>0,"","")
in the long formula with
""
Finally, make the UDF volatile, so that it will recalculate any time there's a change to a cell. You do this by adding the statement:
Application.Volatile
to, say, the empty 4th line, thus
Public Function OneDown(oCell As Range) As Variant
Dim strSName As String
Dim strCAdd As String
Application.Volatile
strSName = Mid(oCell.Formula, 2, InStr(oCell.Formula, "!")  2)
strCAdd = Right(oCell.Formula, Len(oCell.Formula)  InStr(oCell.Formula, "!"))
OneDown = Worksheets(strSName).Range(strCAdd).Offset(1, 0).Value
End Function
This get's rid of the need for NOW() statements in your formulae to force the UDF to recalculate.
Finally, check that all of the values appearing in E1038 are in your lookup table.
If, after making the above changes, you're still having problems, try posting a copy of the offending workbook  makes it much easier to diagnose.
CheersCheers,
Paul Edstein
[MS MVP  Word]

20021214, 23:12 #6
 Join Date
 Jan 2001
 Location
 Illinois
 Posts
 552
 Thanks
 0
 Thanked 1 Time in 1 Post
Re: substitute formula (excel xp)
Thank you for the help. I tried to post an example but its over 2 mg and i can't get it any smaller. The onedown function or something seems to make it too big.
Anyway, i am still getting the n/a error. I changed the substitute to...
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(F21,"I",""),"J", ""),"K","")
and i added application.volate to my function
All the values in the lookup are used.
This is where i'm getting the error
BB 93 A,CE,H,L,O,BS,VS
CE 49 H,L,O
O 27 H,L
H 13 L
BS 56 A,CE,H,L,O
A 46 H,L,O
L 6 #N/A
VS 59 A,CE,H,L,O
This is the formula in the #N/a cell
=IF(ISNUMBER(onedown(B9)),onedown(B9),IF(ISBLANK(o nedown(B9)),"",LOOKUP(LEFT(onedown(B9),1),w2.xls!B BW)&IF(LEN(onedown(B9))>1,CHAR(44)&LOOKUP(MID(oned own(B9),2,1),w2.xls!BBW),"")&IF(LEN(onedown(B9))>2 ,CHAR(44)&LOOKUP(MID(onedown(B9),3,1),w2.xls!BBW), "")&IF(LEN(onedown(B9))>3,CHAR(44)&LOOKUP(MID(oned own(B9),4,1),w2.xls!BBW),"")&IF(LEN(onedown(B9))>4 ,CHAR(44)&LOOKUP(MID(onedown(B9),5,1),w2.xls!BBW), "")&IF(LEN(onedown(B9))>5,CHAR(44)&LOOKUP(MID(oned own(B9),6,1),w2.xls!BBW),"")&IF(LEN(onedown(B9))>6 ,CHAR(44)&LOOKUP(MID(onedown(B9),7,1),w2.xls!BBW), "")&IF(LEN(onedown(B9))>7,CHAR(44)&LOOKUP(MID(oned own(B9),8,1),w2.xls!BBW),"")&IF(LEN(onedown(B9))>8 ,CHAR(44)&LOOKUP(MID(onedown(B9),9,1),w2.xls!BBW), "")&IF(LEN(onedown(B9))>9,CHAR(44)&LOOKUP(MID(oned own(B9),10,1),w2.xls!BBW),"")&IF(LEN(onedown(B9))> 10,CHAR(44)&LOOKUP(MID(onedown(B9),11,1),w2.xls!BB W),IF(NOW()<>0,"",""))))
The cell to the left has this formula...
=csv!$AF$20
This is the "csv" sheet
AF20
TB 93 46 49 13 6
BCDEFGH DEF DEF E
Pur 82 17 32 4 3
BCDEFGH DEF BDEF
Pur Consid 66 15 18 3 3
BCDEFGH DEF DEF
AF20 has a 6 in it and a blank cell at AF21.
This is the onedown function...
Public Function OneDown(oCell As Range) As Variant
Dim strSName As String
Dim strCAdd As String
Application.Volatile
strSName = Mid(oCell.Formula, 2, InStr(oCell.Formula, "!")  2)
strCAdd = Right(oCell.Formula, Len(oCell.Formula)  InStr(oCell.Formula, "!"))
OneDown = Worksheets(strSName).Range(strCAdd).Offset(1, 0).Value
End Function
for now i am putting conditional formatting and turning the cell white if there is an #n/a error. it's all i could think of for now. thank you for your continued help.

20021216, 00:08 #7
 Join Date
 May 2002
 Location
 Canberra, Australian Capital Territory, Australia
 Posts
 5,055
 Thanks
 2
 Thanked 417 Times in 346 Posts
Re: substitute formula (excel xp)
Hi jha,
Just a thought: check whether the source record for the cell giving the N/A error has a space character in it. If the cell looks blank, maybe it's actually just got a space in it. It it's got other characters, maybe there's at space at either end. If this is something that your source data is susceptible to, another change to the substitute string should fix it:
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(F21," I",""),"J",""),"K","")," ","")
Hope this helps. Let me know how you get on.
Cheers
PS: I can't really work with the data in your post, since the relationships and column/row locations aren't clear.Cheers,
Paul Edstein
[MS MVP  Word]