+ Reply to Thread
Results 1 to 11 of 11

How to insert Row without shifting formulas up/down

  1. #1
    Registered User
    Join Date
    08-13-2018
    Location
    Malaysia
    MS-Off Ver
    2016
    Posts
    10

    Question How to insert Row without shifting formulas up/down

    I used this formula
    However, the formula changed whenever I insert new row.

    This formula for 2 workbooks.

    Before insert:
    =SUM([KT.xlsx]First:Last!$D$116)

    After insert:
    =SUM([KT.xlsx]First:Last!$D$117)


  2. #2
    Forum Expert Pepe Le Mokko's Avatar
    Join Date
    05-14-2009
    Location
    Belgium
    MS-Off Ver
    Excel 2010 on Linux - O365
    Posts
    12,694

    Re: How to insert Row without shifting formulas up/down

    Perhaps =SUM(INDIRECT("[KT.xlsx]First:Last!$D$116"))

  3. #3
    Registered User
    Join Date
    08-13-2018
    Location
    Malaysia
    MS-Off Ver
    2016
    Posts
    10

    Re: How to insert Row without shifting formulas up/down

    Quote Originally Posted by Pepe Le Mokko View Post
    Perhaps =SUM(INDIRECT("[KT.xlsx]First:Last!$D$116"))
    Thanks . But, I think this symbol (") should be changed to bracket. But still, got error.

  4. #4
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... based in Ireland... but I could be anywhere in the world
    MS-Off Ver
    Office 365 for PC.
    Posts
    34,981

    Re: How to insert Row without shifting formulas up/down

    =SUM(INDIRECT("'[KT.xlsx]First:Last'!$D$116"))
    Glenn



  5. #5
    Registered User
    Join Date
    08-13-2018
    Location
    Malaysia
    MS-Off Ver
    2016
    Posts
    10

    Re: How to insert Row without shifting formulas up/down

    Quote Originally Posted by Glenn Kennedy View Post
    =SUM(INDIRECT("'[KT.xlsx]First:Last'!$D$116"))
    unfortunately, i got #REF

  6. #6
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... based in Ireland... but I could be anywhere in the world
    MS-Off Ver
    Office 365 for PC.
    Posts
    34,981

    Re: How to insert Row without shifting formulas up/down

    Is the target sheet open and in the same location?

  7. #7
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... based in Ireland... but I could be anywhere in the world
    MS-Off Ver
    Office 365 for PC.
    Posts
    34,981

    Re: How to insert Row without shifting formulas up/down

    Same sort of thing as your other thread. Open both files in the same location.

    =SUMPRODUCT(SUM(INDIRECT("'[SumSource.xlsx]"&$G$1:$G$3&"'!A1")))
    Attached Files Attached Files

  8. #8
    Registered User
    Join Date
    08-13-2018
    Location
    Malaysia
    MS-Off Ver
    2016
    Posts
    10

    Re: How to insert Row without shifting formulas up/down

    Quote Originally Posted by Glenn Kennedy View Post
    Is the target sheet open and in the same location?
    It supposed sum the specific data from all sheets in the second workbook (KT.xlsx)
    Am I answering your question?

  9. #9
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... based in Ireland... but I could be anywhere in the world
    MS-Off Ver
    Office 365 for PC.
    Posts
    34,981

    Re: How to insert Row without shifting formulas up/down

    No.

    Is KT.xlsx OPEN or CLOSED when you try to do the sum. INDIRECT ONLY works on open sheets.

    Is it in the same folder as the sheet you are returning the answer to. If not, the full file path needs to be included.

  10. #10
    Registered User
    Join Date
    08-13-2018
    Location
    Malaysia
    MS-Off Ver
    2016
    Posts
    10

    Re: How to insert Row without shifting formulas up/down

    Quote Originally Posted by Glenn Kennedy View Post
    No.

    Is KT.xlsx OPEN or CLOSED when you try to do the sum. INDIRECT ONLY works on open sheets.

    Is it in the same folder as the sheet you are returning the answer to. If not, the full file path needs to be included.
    Yes, its open and in the same folder

  11. #11
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... based in Ireland... but I could be anywhere in the world
    MS-Off Ver
    Office 365 for PC.
    Posts
    34,981

    Re: How to insert Row without shifting formulas up/down

    See Post 7.

+ 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: 9
    Last Post: 12-04-2015, 05:19 PM
  2. Insert cells, fill formulas down, set formulas for dynamic range
    By Snickers65 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 10-03-2013, 01:43 PM
  3. Formulas for shifting targets.
    By jomili in forum Excel General
    Replies: 3
    Last Post: 10-20-2010, 11:38 AM
  4. Web Query, Stop Formulas Shifting
    By jc0r in forum Excel General
    Replies: 9
    Last Post: 09-21-2008, 01:43 PM
  5. Replies: 2
    Last Post: 06-02-2008, 10:05 AM
  6. [SOLVED] Macro to insert copy and insert formulas only to next blank row
    By bob in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 06-30-2006, 07:10 AM
  7. [SOLVED] Insert New Row without shifting formula
    By DJ in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 09-25-2005, 11:05 PM

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