+ Reply to Thread
Results 1 to 2 of 2

How do i change all my relative references to absolute ones in an automated way?

  1. #1
    Registered User
    Join Date
    12-11-2014
    Location
    Austin, TX
    MS-Off Ver
    2010
    Posts
    10

    How do i change all my relative references to absolute ones in an automated way?

    I have created one row of 737 columns. Basically, i had to convert a well formatted data entry sheet into one row so that it can be uploaded to a work database.

    After creating my one row of 737 columns, I now have discovered that I did not make my references absolute. Thus, when I copy row one, the origin of my reference changes to the next row when pasting on the next line.
    my first row, where cell F9 is the proper reference:
    =IF(ISBLANK('ESA Timesheet'!F9), "", 'ESA Timesheet'!F9)
    my second (pasted row), where the reference still needs to be F9, or more accurately $F$9:
    =IF(ISBLANK('ESA Timesheet'!F10), "", 'ESA Timesheet'!F10)

    Is there a way i can avoid putting in four $s in each of my 737 cells? all the references are in place, i just need to stop the automatic adjustment to the next row, as a relative reference would allow.

  2. #2
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 Version 2403 Win 11 Home 64 Bit
    Posts
    23,811

    Re: How do i change all my relative references to absolute ones in an automated way?

    Look at this link for two different VBA solutions.

    http://www.ozgrid.com/VBA/formula-ref-change.htm
    Alan עַם יִשְׂרָאֵל חַי


    Change an Ugly Report with Power Query
    Database Normalization
    Complete Guide to Power Query
    Man's Mind Stretched to New Dimensions Never Returns to Its Original Form

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. How to for converting absolute references to relative references in formulas
    By edspyhill01 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 02-11-2014, 02:09 AM
  2. Replies: 4
    Last Post: 08-01-2012, 01:34 PM
  3. Replies: 1
    Last Post: 10-29-2009, 06:17 AM
  4. Change from absolute to relative References with Excel Macro.
    By antoni in forum Excel Programming / VBA / Macros
    Replies: 12
    Last Post: 09-14-2009, 02:43 AM
  5. Replies: 4
    Last Post: 07-30-2008, 11:19 PM

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