Results 1 to 7 of 7
  1. #1
    Lounger
    Join Date
    Jul 2001
    Posts
    30
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Find Replace (Excel97 SR1)

    I need to replace a reference to a file name and one value in cells containing a formula such as this:
    ='GRM2001anbpData[01ADOMTH.xls]01 Actual Entry'!G456-'GRM2001anbpData[01ADOMTH.xls]01 Actual Entry'!F456
    Finding all '01' and replacing with '02' in this worksheet. I seem to replace the first cell, but doesn't find any others.
    I'm assuming I should be using the Find/Replace differently. Any Help??

  2. #2
    Star Lounger
    Join Date
    Jan 2001
    Location
    Newcastle, New South Wales, Australia
    Posts
    81
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Find Replace (Excel97 SR1)

    Did you select all the cells first? There is an option in the dialog box for Replace All.
    Ruth

  3. #3
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Find Replace (Excel97 SR1)

    You seem to have forgotten to attach the example. Would you try again?
    Legare Coleman

  4. #4
    Lounger
    Join Date
    Jul 2001
    Posts
    30
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Find Replace (Excel97 SR1)

    I've attached an example of what I'm trying to do. Could you please take a look at it.
    Attached Files Attached Files

  5. #5
    Lounger
    Join Date
    Jul 2001
    Posts
    30
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Find Replace (Excel97 SR1)

    I was able to attach it above.

  6. #6
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Find Replace (Excel97 SR1)

    What is happening is that in step 5 you are asking that the contents of cell B4 be found and replaced. As cell B4 is the only cell with those exact contents, it is the only cell changed. However once you change B4, just copy it down.


    otherwise what you need to do is use a 2 step approach and replace Refer1 with Refer2, and then replace )*1 with )*2. If you just replace 1 with 2, cells from b10 onwards will not be correct.

    Andrew C

  7. #7
    Star Lounger
    Join Date
    Jan 2001
    Location
    Newcastle, New South Wales, Australia
    Posts
    81
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Find Replace (Excel97 SR1)

    Try these:
    Alternative 1:
    select the whole range B3:B12,
    click in the formula bar of B3 to go into edit mode showing
    SUM($A3+Refer1!$A3)*1
    change both the "1"s to "2"s so that the formula becomes
    SUM($A3+Refer2!$A3)*2
    then instead of clicking the green tick, hold down the CTRL key and press enter. This applies the current formula to the whole range and is a short-cut way of copy and paste.

    Alternative 2: edit the formula in B3 as required and click the green tick to ok it. Copy. Select the rest of the range and paste.

    Alternative 3: edit the formula in B3 as required and click the green tick to ok it. Move the cursor onto the bottom right corner of B3 and and double-click. This copies the formula down to the end of the range.

    Also I think you meant Ctrl-V not Ctrl-P ?

    Does this help?
    Ruth

Posting Permissions

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