Closed Thread
Results 1 to 11 of 11

Changing all formula references to absolute in a range of cells

  1. #1
    Registered User
    Join Date
    02-06-2009
    Location
    fl
    MS-Off Ver
    Excel 2003
    Posts
    1

    Changing all formula references to absolute in a range of cells

    I know how to use F4 to toggle between relative and absolute references in a formula when you click on the single reference. But it would be helpful if I could select a whole range of cells containing formulas and make all the references within the selected cells absolute.

    Is there a way? When I search I only get info on the basics of relative vs. absolute and nothing more advanced like this.

  2. #2
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Changing all formula references to absolute in a range of cells

    Here's a macro to accomplish that. Be sure to highlight the range first or it will do the WHOLE sheet and there's not undo.

    Please Login or Register  to view this content.
    (Source)
    _________________
    Microsoft MVP 2010 - Excel
    Visit: Jerry Beaucaire's Excel Files & Macros

    If you've been given good help, use the icon below to give reputation feedback, it is appreciated.
    Always put your code between code tags. [CODE] your code here [/CODE]

    ?None of us is as good as all of us? - Ray Kroc
    ?Actually, I *am* a rocket scientist.? - JB (little ones count!)

  3. #3
    Registered User
    Join Date
    04-20-2009
    Location
    USA
    MS-Off Ver
    Excel 2003
    Posts
    1

    Re: Changing all formula references to absolute in a range of cells

    Thank you for that macro. That is exactly what I came to this forum to find....

  4. #4
    Registered User
    Join Date
    03-12-2009
    Location
    Brisbane, Australia
    MS-Off Ver
    Excel 2010
    Posts
    7

    Re: Changing all formula references to absolute in a range of cells

    Problem is, as soon as you have numerous (number varies) cell references in the formula it stops working and replaces the formula with "#VALUE!". So user beware and always save before using this macro.

  5. #5
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Changing all formula references to absolute in a range of cells

    Quote Originally Posted by ozsupra View Post
    Problem is, as soon as you have numerous (number varies) cell references in the formula it stops working and replaces the formula with "#VALUE!". So user beware and always save before using this macro.
    Sage advice for anyone trying any macro for the first time, eh?

  6. #6
    Registered User
    Join Date
    06-25-2012
    Location
    Plano, TX
    MS-Off Ver
    Excel 2010
    Posts
    1

    Thumbs up Re: Changing all formula references to absolute in a range of cells

    THANK YOU!!!! This code saved me tons of work.... I needed to change over 2,400 cells!

  7. #7
    Registered User
    Join Date
    08-30-2012
    Location
    San Jose, CA
    MS-Off Ver
    Excel 2007
    Posts
    1

    Re: Changing all formula references to absolute in a range of cells

    Thank you for this macro! It is really helpful! Is there a way I can chenge it to Make all the references in the whole workbook absolute? I have a lot of sheets to go through.

  8. #8
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,166

    Re: Changing all formula references to absolute in a range of cells

    shannonu,

    Welcome to the Forum, unfortunately:

    Your post does not comply with Rule 2 of our Forum RULES. Don't post a question in the thread of another member -- start your own thread. If you feel it's particularly relevant, provide a link to the other thread. It makes sense to have a new thread for your question because a thread with numerous replies can be off putting & difficult to pick out relevant replies.
    If I have helped, Don't forget to add to my reputation (click on the star below the post)
    Don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved)
    Use code tags when posting your VBA code: [code] Your code here [/code]

  9. #9
    Registered User
    Join Date
    05-01-2013
    Location
    USA
    MS-Off Ver
    Excel 2007
    Posts
    1

    Re: Changing all formula references to absolute in a range of cells

    Just used this today... Brilliant!

  10. #10
    Registered User
    Join Date
    07-08-2013
    Location
    Shelburne, Ontario
    MS-Off Ver
    Excel 2007
    Posts
    1

    Re: Changing all formula references to absolute in a range of cells

    I'm a self-taught Excel user, can someone explain how to use this macro in a range of cells? I don't understand how to enter it. Thanks

  11. #11
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Changing all formula references to absolute in a range of cells

    How/Where to install the macro:

    1. Open up your workbook
    2. Get into VB Editor (Press Alt+F11)
    3. Insert a new module (Insert > Module)
    4. Copy and Paste in your code (given above)
    5. Get out of VBA (Press Alt+Q)
    6. Save as a macro-enabled workbook

    The macro is installed and ready to use. Press Alt-F8 and select it from the macro list.


    Karen, if you're not familiar with VBA, be very careful with this. Make sure you run it on a COPY of your data, there's no UNDO from what a macro does.

    As per forum rules you should ask further questions regarding the specifics of what you're trying to do in a thread of your own.

Closed Thread

Thread Information

Users Browsing this Thread

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

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