+ Reply to Thread
Results 1 to 4 of 4

Keeping the cell reference for SUMIF when deleting the data it references

  1. #1
    Registered User
    Join Date
    09-09-2013
    Location
    Australia
    MS-Off Ver
    Excel 2010
    Posts
    35

    Keeping the cell reference for SUMIF when deleting the data it references

    Hi All,

    I have a table (let's call it report) that I populate using the SUMIF function. Basically it looks for data of a certain type and sums it. The data is on another worksheet in the same workbook.

    What happens is that every month I will copy the report table to another worksheet for historical reference. I then delete the 'data' table. What happens is that I will get #REF! errors on all my SUMIF functions. I have to go through the pain of resetting all of them.

    An example of one of the codes I use is

    Please Login or Register  to view this content.
    How do I keep the function from getting these errors and having to fix them each time?

    Thanks all.

  2. #2
    Forum Guru :) Sixthsense :)'s Avatar
    Join Date
    01-01-2012
    Location
    India>Tamilnadu>Chennai
    MS-Off Ver
    2003 To 2010
    Posts
    12,771

    Re: Keeping the cell reference for SUMIF when deleting the data it references

    The problem is occurring because you are deleting the entire rows/range. Instead of deleting the rows/range just delete the CONTENTS/FORMATS alone by using Alt+E+A+A. So the source reference will not getting deleted.


    If your problem is solved, then please mark the thread as SOLVED>>Above your first post>>Thread Tools>>
    Mark your thread as Solved


    If the suggestion helps you, then Click *below to Add Reputation

  3. #3
    Registered User
    Join Date
    09-09-2013
    Location
    Australia
    MS-Off Ver
    Excel 2010
    Posts
    35

    Re: Keeping the cell reference for SUMIF when deleting the data it references

    Tried it and it works. Thanks!

    Rep added :D

  4. #4
    Forum Guru :) Sixthsense :)'s Avatar
    Join Date
    01-01-2012
    Location
    India>Tamilnadu>Chennai
    MS-Off Ver
    2003 To 2010
    Posts
    12,771

    Re: Keeping the cell reference for SUMIF when deleting the data it references

    Glad it helps you and thanks for the feedback

    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

+ 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. Deleting Row and Keeping just care data
    By bigman8424 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 06-17-2013, 12:22 AM
  2. [SOLVED] Deleting data while keeping formulas
    By kehlerah in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 12-28-2012, 10:13 AM
  3. Keeping cell references in formula the same when cells are inserted
    By MSPeterson in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 09-16-2011, 05:11 PM
  4. [SOLVED] Keeping cell references constant
    By FlyingDutchmanIam in forum Excel General
    Replies: 1
    Last Post: 06-29-2006, 06:50 AM
  5. keeping named cell references.
    By steamboat in forum Excel General
    Replies: 0
    Last Post: 02-06-2006, 03:25 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