Page 1 of 2 12 LastLast
Results 1 to 15 of 19
  1. #1
    2 Star Lounger
    Join Date
    Feb 2001
    Posts
    107
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Links and replace data (Excel 97)

    Good morning everybody!
    I have the following problem. I have two files, one with the 'raw' data as I call it, and the second one is linked to the first one, with the report in a nice and presentable format.
    As I am working with % change data in some columns of my report, sometimes, depending on my values I have -100. Somebody asked me if I can replace the -100 with NA (not available), it would just look better, she said. Now, I can only make modifications to the linked file, I cannot touch the original file with the 'raw' data.
    I was thinking in the lines of an IF formula in each cell, which would work, but this solution is very very tedious and long, as I am working with practically hundreds of cells like these, scattered across 10-12 sheets. It would take me ages to input this formula in every cell, even if I could drag down the formula to other cells below. Also my link looks like this for example:
    ='D:seriesClientCountryCategory[rawdata.xls]Total'!F7 . If I will use an IF formula, it will become also very, very long:
    =if(='D:seriesClientCountryCategory[rawdata.xls]Total'!F7 =-100, 'NA', 'D:seriesClientCountryCategory[rawdata.xls]Total'!F7).
    Can anyone think of an easier way to accomplish this?
    I also thought of a macro, which searches for -100 and replaces them with NA's, but for that I need to have real values, not links in my file.
    Any help, hints very welcome and appreciated.
    K.

  2. #2
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Links and replace data (Excel 97)

    You could use format, conditional format and set the cells that are equal to -100 (-1 ??) to have a white font.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  3. #3
    2 Star Lounger
    Join Date
    Feb 2001
    Posts
    107
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Links and replace data (Excel 97)

    I thought about this too, but unfortunately it wouldn't work as the person wants explicitely NA instead of -100, and not an 'empty' cell.
    But thanks anyway for your kind post.
    Any oder ideas maybe?
    Regards,
    K.

  4. #4
    Lounger
    Join Date
    Oct 2001
    Location
    Isleworth, Gtr London, England
    Posts
    28
    Thanks
    0
    Thanked 2 Times in 2 Posts

    Re: Links and replace data (Excel 97)

    Use a custom number format to display -100 as NA. Choose menu command Format/Cells and click the Number tab, and select Category "Custom" from the list on the left hand side, and then in the Type input box type this :
    [=-100]"NA";#0
    and click the OK button, and that should do what you want.

    Glenn Bumford

  5. #5
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Links and replace data (Excel 97)

    What about this macro:

    Option Explicit

    Sub MakeIfs()
    Dim rCell As Range
    Dim sOldFormula As String
    Dim sNewFormula As String
    Dim sElse As String
    Dim sCondition As String
    sCondition = "=-100"
    sElse = """NA"""
    For Each rCell In ActiveSheet.UsedRange.Cells
    sOldFormula = rCell.Formula
    If Left(sOldFormula, 1) = "=" Then
    sOldFormula = Right(sOldFormula, Len(sOldFormula) - 1)
    sNewFormula = "=IF(" & sOldFormula _
    & sCondition & "," & sElse & "," & sOldFormula & ")"
    rCell.Formula = sNewFormula
    End If
    Next
    End Sub
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  6. #6
    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: Links and replace data (Excel 97)

    Nifty! [ <img src=/S/bow.gif border=0 alt=bow width=15 height=15> I have to remember that.

    Cheers
    [b]Catharine Richardson (WebGenii)
    WebGenii Home Page
    Moderator: Spreadsheets, Other MS Apps, Presentation Apps, Visual Basic for Apps, Windows Mobile

  7. #7
    2 Star Lounger
    Join Date
    Feb 2001
    Posts
    107
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Links and replace data (Excel 97)

    Thanks for all your responses, they are very useful. I think, however, the the best one that suits my needs is the custom cell formatting, because it seems the easiest solution from all. I will keep the macro in my list of useful macros, but for now, I simply took the formatting given and modified it to also show the negative numbers with red font and in brackets. I have a very minor problem with it though, that even the 0 is shown as negative now. I can't figure out why... Can you please help me again?
    Here is what I did so far:
    #,##0;[=-100]"NA";<font color=red>(#,##0)

    Thanks again for all your help.
    K.

  8. #8
    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: Links and replace data (Excel 97)

    Try this
    #,##0;[=-100]"NA";General

    For those following this thread -
    you have 4 settings you can control in a custom format, each setting is separated from the others by a semicolon.
    first position is for positive number format;second position is for negative number format;third position is for zero;fourth position is for text.

    Cheers
    [b]Catharine Richardson (WebGenii)
    WebGenii Home Page
    Moderator: Spreadsheets, Other MS Apps, Presentation Apps, Visual Basic for Apps, Windows Mobile

  9. #9
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Links and replace data (Excel 97)

    #,##0_);[=-100]"N/A";0_; also works. He needed to remove the red formatting from the last argument, and he loses ability to further format negative numbers. Personally I'd go for using NA() somewhere in the formula.

    I didn't know you can use text names like that! It's not in Help. But "General" seems to be the only one that works in XL97; are there others?
    -John ... I float in liquid gardens
    UTC -7ąDS

  10. #10
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Links and replace data (Excel 97)

    I see what the problem is; when you put [ red ] in -without- the spaces, the Lounge is interpreting it as turning the post message font red. So for custom formats in Excel your posted word [ red ] is not showing up. Put in spaces like I have here, then tell everyone not to use the spaces in Excel.
    -John ... I float in liquid gardens
    UTC -7ąDS

  11. #11
    Lounger
    Join Date
    Oct 2001
    Location
    Isleworth, Gtr London, England
    Posts
    28
    Thanks
    0
    Thanked 2 Times in 2 Posts

    Re: Links and replace data (Excel 97)

    You can have more than one section with a clause in it. Try this
    [=-100]"NA";[ red ][<0](#,##0);#,##0_)
    which will make your negatives red with brackets round. ( Leave the spaces out of [ red ], as I've done this to stop it being mistaken for a posting font control string. )

    Glenn Bumford

  12. #12
    Lounger
    Join Date
    Oct 2001
    Location
    Isleworth, Gtr London, England
    Posts
    28
    Thanks
    0
    Thanked 2 Times in 2 Posts

    Re: Links and replace data (Excel 97)

    Thanks John, have edited my posting now, so should make more sense.

    Anyway, the rules for conditions of your own in Custom Formats is very similar to the standard, except that when you create conditions the rules go something like
    [condition 1]number format 1 ; [condition 2]number format 2;number format 3;text
    where number format 3 is applied to cells that fail conditions 1 and 2.

    Glenn Bumford

  13. #13
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Links and replace data (Excel 97)

    This is a cool tip Glenn. In playing with it in XL97, it appears that 1. there can only be one "conditional" format per custom format argument and 2. we are restricted to the standard four arguments (pos, neg, zero, text); therefore we can't use multiple conditional formats for any particular one (of the four) format argument sets. Or am I missing something?
    -John ... I float in liquid gardens
    UTC -7ąDS

  14. #14
    Lounger
    Join Date
    Oct 2001
    Location
    Isleworth, Gtr London, England
    Posts
    28
    Thanks
    0
    Thanked 2 Times in 2 Posts

    Re: Links and replace data (Excel 97)

    No, you're not missing anything. The four arguments limit, and the fact that only one condition is allowed per argument means that it's usefulness is quite limited, but when your needs can be met this way it's a quick way to achieve something nifty very quickly.

    Glenn Bumford

  15. #15
    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: Links and replace data (Excel 97)

    I don't think so .... but hey anything is possible!

    Cheers
    [b]Catharine Richardson (WebGenii)
    WebGenii Home Page
    Moderator: Spreadsheets, Other MS Apps, Presentation Apps, Visual Basic for Apps, Windows Mobile

Page 1 of 2 12 LastLast

Posting Permissions

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