• Links and replace data (Excel 97)

    Home » Forums » AskWoody support » Productivity software by function » MS Excel and spreadsheet help » Links and replace data (Excel 97)

    Author
    Topic
    WSKislany
    AskWoody Lounger
    October 17, 2001 at 5:50 am #361618

    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.

    Reply | Quote
    Viewing 2 reply threads
    Author
    Replies
    • WSpieterse
      AskWoody Lounger
      October 17, 2001 at 5:58 am #547171

      You could use format, conditional format and set the cells that are equal to -100 (-1 ??) to have a white font.

      Reply | Quote
      • WSKislany
        AskWoody Lounger
        October 17, 2001 at 6:40 am #547177

        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.

        Reply | Quote
        • WSpieterse
          AskWoody Lounger
          October 17, 2001 at 7:41 am #547180

          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

          Reply | Quote
    • WSGlennB
      AskWoody Lounger
      October 17, 2001 at 7:36 am #547179

      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

      Reply | Quote
      • WSWebGenii
        AskWoody Lounger
        October 17, 2001 at 11:20 am #547196

        Nifty! [ bow I have to remember that.

        Cheers

        Reply | Quote
      • WSKislany
        AskWoody Lounger
        October 17, 2001 at 2:07 pm #547238

        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”;(#,##0)

        Thanks again for all your help.
        K.

        Reply | Quote
        • WSWebGenii
          AskWoody Lounger
          October 17, 2001 at 2:24 pm #547239

          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

          Reply | Quote
          • WSJohnBF
            AskWoody Lounger
            October 17, 2001 at 2:49 pm #547241

            #,##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?

            Reply | Quote
            • WSWebGenii
              AskWoody Lounger
              October 17, 2001 at 5:48 pm #547278

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

              Cheers

              Reply | Quote
        • WSGlennB
          AskWoody Lounger
          October 17, 2001 at 4:29 pm #547255

          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

          Reply | Quote
          • WSJohnBF
            AskWoody Lounger
            October 17, 2001 at 3:59 pm #547260

            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.

            Reply | Quote
            • WSGlennB
              AskWoody Lounger
              October 17, 2001 at 4:34 pm #547266

              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

              Reply | Quote
            • WSJohnBF
              AskWoody Lounger
              October 17, 2001 at 4:57 pm #547269

              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?

              Reply | Quote
            • WSGlennB
              AskWoody Lounger
              October 17, 2001 at 5:09 pm #547270

              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

              Reply | Quote
        • WSAndrew Cronnolly
          AskWoody Lounger
          October 18, 2001 at 7:42 am #547285

          Does [>=0]#,##0;[=-100]”NA”;[ red] (#,##0) help ?

          Whilst the formatting sections cover Positive, Negative,Zero and text under normal circumstances, in effect the thgird section applies to any numbers not covered by either the first or second sections, so that if conditions apply to those it could upset what the third section applies to.

          Andrew C

          Reply | Quote
    • WSMichael Evans
      AskWoody Lounger
      October 17, 2001 at 10:19 pm #547333

      Though others have solved your problem, the following is sometimes useful. An alternative to filling down &/or across is to select all the cells you want the formula entered in, type the formula in the formula bar, then hold down Ctrl and press Enter, this enters the formula in all the selected cells. It works on multiple selections.

      Reply | Quote
      • WSKislany
        AskWoody Lounger
        October 18, 2001 at 8:12 am #547397

        Cheers to everybody!
        I want to say a great thanks to all of you. Some gave me a direct help, others useful tips which are worthwhile keeping.
        I will print out the whole thread and chew through it carefully.
        You are the greatest!

        K.

        Reply | Quote
      • WSColinBurrows
        AskWoody Lounger
        October 18, 2001 at 11:42 am #547417

        I find I use this all the time. 9 times out of 10 I want to copy the formula but not the formatting. Usually the formula is already entered, so I use a minor variation. Select all the cells, including the source cell with the formula to be copied (the source cell should be the active cell.) Press F2, then Control+Enter. Effectively it’s equivalent to Control D or Control R (copy down or copy right), except the formatting isn’t copied.

        Reply | Quote
    Viewing 2 reply threads
    Reply To: Links and replace data (Excel 97)

    You can use BBCodes to format your content.
    Your account can't use all available BBCodes, they will be stripped before saving.

    Your information:




DON'T MISS OUT!
Subscribe to the Free Newsletter
We promise not to spam you. Unsubscribe at any time.
Invalid email address

View the Forum

  • Recent Replies
  • My Replies
  • My Active Topics
  • New Posts in the Last day
  • Private Messages
  • Knowledge Base
  • How to use the Forums
  • All Forums

    • Recent Topics

    My Profile

    April 2025
    S M T W T F S
     12345
    6789101112
    13141516171819
    20212223242526
    27282930  

    Remembering Woody

     

    Want to Advertise in the free newsletter? How about a gift subscription in honor of a birthday? Send an email to sb@askwoody.com to ask how.

    Mastodon profile for DefConPatch
    Mastodon profile for AskWoody

     

    Home • About • FAQ • Posts & Privacy • Forums • My Account
    Register • Free Newsletter • Plus Membership • Gift Certificates • MS-DEFCON Alerts

    Copyright ©2004-2025 by AskWoody Tech LLC. All Rights Reserved.