+ Reply to Thread
Results 1 to 6 of 6

Find and replace in formulas

  1. #1
    Registered User
    Join Date
    07-11-2009
    Location
    aarhus
    MS-Off Ver
    Excel 2003
    Posts
    24

    Find and replace in formulas

    Hello

    I have various formulas (such as: =(D3-$E$3)/$E$3 )
    which continues for each row (IE next row 3 is replaced with 4 etc)

    I have made som errors when I made the formulas and therefore I have the following questions:

    1. Is it possible to find and replace all E's with another letter (F in my case). I haven't been able to resolve this, when it has to be done inside formulas?
    The optimal would be if I could set 'Display formulas' under tools, and find and replace in the formulas displayed.

    2. Is there a smart way of swapping D3 with E3 for all formulas? (So the result will be: =($E$3-D3)/$E$3 and etc for all formulas)

    Thanks in advance.
    Last edited by naquer; 07-20-2009 at 04:53 PM.

  2. #2
    Forum Expert oldchippy's Avatar
    Join Date
    02-14-2005
    Location
    Worcester, UK
    MS-Off Ver
    Excel 2007 (Home)
    Posts
    7,097

    Re: Find and replace in formulas

    Hi,

    Something like this?
    Attached Images Attached Images
    oldchippy
    -------------


    Blessed are those who can give without remembering and take without forgetting

    If you are happy with the help you have received, please click the <--- STAR icon on the left - Thanks.

    Click here >>> Top Excel links for beginners to Experts

    Forum Rules >>>Please don't forget to read these

  3. #3
    Registered User
    Join Date
    07-11-2009
    Location
    aarhus
    MS-Off Ver
    Excel 2003
    Posts
    24

    Re: Find and replace in formulas

    Of course :-), Thanks !

    No idea how I overlooked that

    However I want to change the order in all my formulas, so that the next row will be
    =($E$4-D4)/$E$4

    I have about 100 rows, so it's alot of work if i have to search and replace for each row.

    I also drag the formulas the continue so that the next formula is =($E$4-E4)/$E$4, therefore removing the $ is not the solution.

  4. #4
    Forum Expert oldchippy's Avatar
    Join Date
    02-14-2005
    Location
    Worcester, UK
    MS-Off Ver
    Excel 2007 (Home)
    Posts
    7,097

    Re: Find and replace in formulas

    Hi,

    Take a look here

    http://excel.tips.net/Pages/T003067_...a_Pattern.html

    Does that help?

  5. #5
    Registered User
    Join Date
    07-11-2009
    Location
    aarhus
    MS-Off Ver
    Excel 2003
    Posts
    24

    Re: Find and replace in formulas

    Thanks alot for the answer. This will for sure help me out in other situations.

    However the solution to my problem showed to something as simple as this:


    Answer
    You need an ABSOLUTE reference which is $A$1 - which you can do by pressing the F4 key with the cursor on the A1 in the formula bar (or just type the dollars)

    For reference

    $A1 would always refer to column A, but variable rows
    A$1 would always refer to row one but variable columns



    Thanks again though!

  6. #6
    Forum Expert oldchippy's Avatar
    Join Date
    02-14-2005
    Location
    Worcester, UK
    MS-Off Ver
    Excel 2007 (Home)
    Posts
    7,097

    Re: Find and replace in formulas

    If you are satisfied with the solution(s) provided, please mark your thread as Solved.

    How to mark a thread Solved
    Go to the first post
    Click edit
    Click Go Advanced
    Just below the word Title you will see a dropdown with the word No prefix.
    Change to Solved
    Click Save

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1