+ 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
    O365 v 2402
    Posts
    13,443

    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... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,900

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

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




    None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.

    Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh

  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... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,900

    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... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,900

    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... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,900

    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... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,900

    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. 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