+ Reply to Thread
Results 1 to 8 of 8

Deleting data while keeping formulas

  1. #1
    Registered User
    Join Date
    12-28-2012
    Location
    Singapore
    MS-Off Ver
    Excel 2007
    Posts
    4

    Deleting data while keeping formulas

    hello, I have a workbook w multiple worksheets which will tabulate stats based on the data I enter into one of the worksheets labelled 'Data'. How do I delete the data from that 'Data' worksheet without affecting the formulas on the worksheet itself and still make sure that the other worksheets will tabulate the new stats according to the new data I enter in?

  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: Deleting data while keeping formulas

    If you don't delete the CELL(s) / ROW(s) / COLUMN(s) of that worksheet or even if you don't delete that SHEET then the formula's will never get affected


    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
    12-28-2012
    Location
    Singapore
    MS-Off Ver
    Excel 2007
    Posts
    4

    Re: Deleting data while keeping formulas

    I tried to do the thing where I go F5 -> Go To -> Special -> Select Constants and tick Numbers and Text -> Ok, which would select all the data and then I deleted it, it works, like the formulas on that worksheet remain, but the stats on the other worksheets dont change! How do I make it such that after deleting the data, when I enter in the new data, the stats on the other worksheets will change as well? Or do they not change automatically and I have click some button in order for the stats to be tabulated from the new data into the other worksheets?

  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: Deleting data while keeping formulas

    I believe the calculation mode of that workbook is set to manual. Press F9 and check whether there is any change in formula result if it yes then change the calculation mode of that workbook to Automatic.

    Sent from mobile device

  5. #5
    Registered User
    Join Date
    12-28-2012
    Location
    Singapore
    MS-Off Ver
    Excel 2007
    Posts
    4

    Re: Deleting data while keeping formulas

    Just checked and it's auto! I think the spreadsheet uses the PivotTable thing such that the data entered under the 'Data' tab is supposed to change the statistical values on the other tabs, just that it doesn't work now; after I delete the data in the 'Data' tab, the stats on the other tabs still remain the same! D: hehe any idea how to make it work?

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

    Re: Deleting data while keeping formulas

    Thanks for giving a clue (Pivot Table) just place the cursor inside the pivot table data and do right click and click REFRESH. whenever any change happen in source data then PIVOT TABLE requires REFRESH to get the changes reflected.

    Sent from mobile device so regret for the delayed reply.

  7. #7
    Registered User
    Join Date
    12-28-2012
    Location
    Singapore
    MS-Off Ver
    Excel 2007
    Posts
    4

    Red face Re: Deleting data while keeping formulas

    omg you're a genius!!!! haha THANK YOUUUUU!!!!!!! Much appreciated!!
    hehe sorry for spamming so many questions haha I'm vv bad at this kinda things!!

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

    Re: Deleting data while keeping formulas

    Glad, your problem is solved and thanks for the feedback

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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