Closed Thread
Page 2 of 2 FirstFirst 1 2
Results 16 to 19 of 19

auto-fill absolute references

  1. #16
    Registered User
    Join Date
    03-24-2008
    Posts
    11

    Re: auto-fill absolute references

    Since you do not want to use indirect() or row(), the only other option I can propose is VBA. You can use the folloiwng code:
    Please Login or Register  to view this content.
    Select a set of cells where you want the formulae (in a single column), and then run the above macro.
    Last edited by fiate2000; 07-18-2011 at 06:24 PM.

  2. #17
    Registered User
    Join Date
    11-04-2017
    Location
    Honolulu, Hawaii, U.S.A.
    MS-Off Ver
    2007
    Posts
    1

    Re: auto-fill absolute references

    I had been browsing online for method(s) to accomplish exactly what the original post wanted to achieve. And instead of "auto-fill" or "copy/paste" method which will not work correctly for absolute references, I found a post with a workaround solution that is both easy and fast; assuming it's more than just a few cells needing to be changed from relative to absolute references. Kudos to JBeaucaire!

    1. Auto-fill as you normally would, for now just leave the cells as relative references.
    2. Highlight the range of cells you want to change from relative reference to absolute reference.
    3. Insert the macro as shown in post #2: https://www.excelforum.com/excel-for...-of-cells.html
    4. MAKE SURE TO HIGHLIGHT THE RANGE BEFORE RUNNING THE MACRO

  3. #18
    Registered User
    Join Date
    12-21-2017
    Location
    Salt Lake City, UTAH
    MS-Off Ver
    Microsoft Office Home and Business 2016
    Posts
    1

    Re: auto-fill absolute references

    I know it has been a number of years ago, but I have been dealing with this same problem myself for quite some time also. I just now figured out a real easy solution:
    Go ahead and auto fill your cell references with only the Column specified as absolute and the row as relative (e.g. "=$C2").
    Then auto-fill down to however far you need to go.
    THEN, Select & highlight the filtered area (in your case, you said you needed to go 100 rows) (e.g. cells C1 through C100).
    Use Find & Replace Feature by Selecting the "Find & Select" button on the top toolbar and select "Replace".
    In the "Find what" field, type "$c"
    In the "Replace with" field, type "$c$"
    Click "Replace All" button on the bottom of that dialog box.
    Wham! Done! Easy-peasy! Knew there was a way!
    Let me know your feedback!

  4. #19
    Registered User
    Join Date
    01-03-2018
    Location
    Tampa, FL
    MS-Off Ver
    Office 2016
    Posts
    1

    Re: auto-fill absolute references

    BBBRaptor, I joined solely to say your fix worked for my 1500 row spreadsheet! Changed like 100,000 references in an epic function drive sheet.

    Unfortunately it does not solve the problem me and the original OP wanted to solve which was avoiding the cell reference changing when you insert or a delete a row above or below the absolute references. If I reference $A$1502 in cell A6 (this is actually what I do) and delete row 1500 it still changes the reference to $A$1501.

    But if you just want a ton of absolute references this works awesome thanks!

Closed Thread
Page 2 of 2 FirstFirst 1 2

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