Share via

Formulas display contents from another sheet that are deleted

Anonymous
2010-09-18T17:09:09+00:00

This a sample table in sheet1:

A B C D E
1 Path\File name Bit rate Type Title Artist
2 album1\01-track01.mp3 256 mp3 Song title 1 Singer 1
3 album1\02-track02.mp3 256 mp3 Song title 2 Singer 2
... ...
3900 album2000\12-track12.flac 895 flac Song title 12 Singer #x

Then I display some of those data in sheet2 in a different order, like this:

A B C
1 Title Artist Path\File name
2 =sheet1!D2 =sheet1!E2 =sheet1!A2
3 =sheet1!D3 =sheet1!E3 =sheet1A3
... ...
3900 =sheet1!D3900 =sheet1!E3900 =sheet1!A3900

Well, if I select the cells A2::E3900 in sheet1 and delete their contents, the cells in sheet2 should display zeros. Right?

Wrong!!!

In my case, some of those cells display zeros, while the rest of them display the data of sheet1, as If they are not deleted!!!

Furthermore, into the empty cells of the table in sheet1, I paste the contents from another workbook (paste -> as values). Now, some cells in sheet2 display the new data, while the rest of them still display the deleted data!!!

Why Excel doesn't remember that I've already deleted those contents? Does it suffers from Alzheimer's disease?

Microsoft 365 and Office | Excel | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

9 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2010-09-18T23:02:21+00:00

    Does this width change matter?

    No, that is normal behaviour in Show Formulas mode. The column are automagically extended as most formulas are wider than the values they represent.

    With those possibilities out of the way, I could only suggest some form of cell locking within document protection (Review tab, Changes group, Protect Sheet options). Is it under a form of Protect Worksheet with some cells formatted as locked and some not?

    Hit CTRL+A (Select All), then right click for Format, Protection tab. Is the Locked option checked, unchecked or semi-checked (checkbox colour-filled). If semi-checked either lock or unlock then all and check the Protect Sheet options as above.

    (instructions as above for Excel 2007, Excel 2010 may vary slightly)

    • If this proposed solution has resolved your issue(s), please return and mark it as Answered for others to consider.

    Was this answer helpful?

    0 comments
  2. Anonymous
    2010-09-18T21:15:41+00:00

    To confirm the use of formulas rather than values in Sheet2, scroll to the area of cells that is not updating and press CTRL+<accent grave> to display formulas instead of values. The accent grave (` ) is the lower case tilde (~ ) key usually located just above the TAB on a standard 104-key keyboard.

    I only suggest this because you've mentioned the use of Paste Special, Values and I'm wondering if the wrong sheet had been selected for such a paste.

    BTW, using a Custom Number Format like _(* #0_);_(* (#0);_(* "-"??_);_(@_) on Sheet2 may be more aesthetically pleasing for cells referencing blank entries on Sheet1, e.g. a hyphen rather than a zero.

    The paste special > values was in sheet1, indeed. In fact, I did CTRL+ in sheet2 and the formulas are there, as they should. However, when I did this, the width of all the columns extended at least twice of the previous size (before CTRL+) and then, they went back to the original width, when I re-used CTRL+` to display the contents.

    Does this width change matter?

    Was this answer helpful?

    0 comments
  3. Anonymous
    2010-09-18T20:54:43+00:00

    Check to see if your "Recalculation" is set to "Manual" rather than "Automatic"

    Otherwise, you may have some macros coming in to play and making the values what they want to.

    Calculation is set to automatic, alright. I have never changed it.

    There are no macros at all.

    Was this answer helpful?

    0 comments
  4. Anonymous
    2010-09-18T18:00:37+00:00

    To confirm the use of formulas rather than values in Sheet2, scroll to the area of cells that is not updating and press CTRL+<accent grave> to display formulas instead of values. The accent grave (`) is the lower case tilde (~) key usually located just above the TAB on a standard 104-key keyboard.

    I only suggest this because you've mentioned the use of Paste Special, Values and I'm wondering if the wrong sheet had been selected for such a paste.

    BTW, using a Custom Number Format like _(* #0_);_(* (#0);_(* "-"??_);_(@_) on Sheet2 may be more aesthetically pleasing for cells referencing blank entries on Sheet1, e.g. a hyphen rather than a zero.

    • If this proposed solution has resolved your issue(s), please return and mark it as Answered for others to consider.

    Was this answer helpful?

    0 comments
  5. Anonymous
    2010-09-18T17:26:00+00:00

    Check to see if your "Recalculation" is set to "Manual" rather than "Automatic"

    Otherwise, you may have some macros coming in to play and making the values what they want to.

    hth

    Vaya con Dios,

    Chuck, CABGx3

    Was this answer helpful?

    0 comments