Results 1 to 7 of 7
  1. #1
    4 Star Lounger
    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

  2. #2
    Super Moderator WebGenii's Avatar
    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

  3. #3
    WS Lounge VIP sdckapr's Avatar
    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

  4. #4
    4 Star Lounger
    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.

  5. #5
    Super Moderator
    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.

    Cheers
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  6. #6
    4 Star Lounger
    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.

  7. #7
    Super Moderator
    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]

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •