Forum Discussion
Data disappears from worksheets.
My excel sheet is acting weird. I have multiple sheets linked together to perform sales and expenses analysis it all works out find when I open the file but once I enter a data on one of your worksheet that is linked to another worksheet, the data on the other worksheets is being deleted
Some of the cells on the Master sheet contain circular references: A3581 to A3589, and A3613/A3617.
Does the problem persist if you correct these by double-clicking the fill handle in A3, or by changing the formula in A2:A3925 to =ROW()-1 ?
19 Replies
It appears there are over 41k conditional formats on the master sheet?
While googling to see what excel's conditional format limits are, I happened across this post in which a person experienced problems w/ the application freezing/crashing when testing with large numbers of format conditions.
I deleted the format conditions in the attached file. Perhaps try opening the file and test to see if it runs any better?
- If a friend set the workbook up, then I would look for functions she is using that you may not have due to different excel versions. That would be one explanation why it works for her, but not you and why the data "disappears" (the data is there when the book opens, but disappears when excel recalculates).
For example, I don't have the CONCAT function (Daily Sale worksheet, cell B4), so the formula looks like this (note the xlfn.CONCAT - the "xlfn." prefix means I don't have the CONCAT function, so it evaluates to zero for me).
=SUMIFS(Master!$S$2:$S$3946,Master!$D$2:$D$3946,"Sale",Master!$B$2:$B$3946,_xlfn.CONCAT("=",TEXT($B$2+A4-1,"DD/MM/YYYY")))
But, I can change it to:
=SUMIFS(Master!$S$2:$S$3946,Master!$D$2:$D$3946,"Sale",Master!$B$2:$B$3946,$B$2+A4-1)
and get the column to populate (though the values should be confirmed to make sure it's returning correct results - it appears the dates on the master sheet are all numeric so I believe it will work, but I didn't set it up). Your friend might be able to help with changing the formula to use functions you don't have.
Some of the cells on the Master sheet contain circular references: A3581 to A3589, and A3613/A3617.
Does the problem persist if you correct these by double-clicking the fill handle in A3, or by changing the formula in A2:A3925 to =ROW()-1 ?
Here is the file after I followed your instructions. Still has the same problem. My friend tried opening it on a Window machine and it seems to have worked and sent the saved version to me. It worked when I opened it but as soon as I did something the master sheet, all the data in other worksheet disappeared.
I cannot reproduce the problem, but like your friend I'm on Windows.
Are you on MacOS? If so, I hope that someone with Excel for Mac can look at it.
