+ Reply to Thread
Results 1 to 4 of 4

Keeping formulas the same when shifting what they are linked to

  1. #1
    Registered User
    Join Date
    04-05-2020
    Location
    Central New York, United States
    MS-Off Ver
    Office 16
    Posts
    2

    Keeping formulas the same when shifting what they are linked to

    Hi! I have a spreadsheet that I use for grading student labs.
    My goal is that I'll have a formula in E2 that references the line below (i.e. =e3), and **stays** set to =e3 when I swap out the line below it.
    Unfortunately, each time I move or delete the line below, the formula in E2 changes to accommodate the change, or has a reference error in the case of a line deletion. Is there a way to prevent this from happening? (I tried using =$e3, but that didn't help.)
    If I am not making clear what I am trying to do, please let me know that, too.
    Thanks in advance to anyone who can share insights on this.
    Sincerely,
    Josh (i.e. Physics_Singer)
    Last edited by Physics_Singer; 04-05-2020 at 04:07 PM. Reason: Solved

  2. #2
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    22,768

    Re: Keeping formulas the same when shifting what they are linked to

    Try it like this:

    =INDIRECT("E3")

    Hope this helps.

    Pete

  3. #3
    Registered User
    Join Date
    04-05-2020
    Location
    Central New York, United States
    MS-Off Ver
    Office 16
    Posts
    2

    Re: Keeping formulas the same when shifting what they are linked to

    That's perfect!!! Thank you so much, Pete!!!!

  4. #4
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    22,768

    Re: Keeping formulas the same when shifting what they are linked to

    You're welcome, Josh - glad to help.

    If that takes care of your original question, please take a moment to select Thread Tools from the menu above and to the right of your first post in this thread, and mark the thread as SOLVED.

    Also, since you are relatively new to the forum, you might like to know that you can directly thank those who have helped you by clicking on the small "star" icon located in the lower left corner of a post that you have found to be helpful (not just in this thread - for any post that has helped you). This also adds to the reputation of the poster (the small green bars in the poster's profile).

    Pete

+ 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. Replies: 10
    Last Post: 08-14-2018, 11:50 AM
  2. Replies: 2
    Last Post: 03-24-2018, 09:37 AM
  3. Replies: 4
    Last Post: 04-24-2013, 04:58 AM
  4. Formulas for shifting targets.
    By jomili in forum Excel General
    Replies: 3
    Last Post: 10-20-2010, 11:38 AM
  5. Web Query, Stop Formulas Shifting
    By jc0r in forum Excel General
    Replies: 9
    Last Post: 09-21-2008, 01:43 PM
  6. Replies: 2
    Last Post: 06-02-2008, 10:05 AM
  7. Keeping the same format in linked worksheets
    By Confused in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 08-03-2006, 08:55 AM

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