Data disappears from worksheets.

[Panas_Wiwat-1964]

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

[#2 · Accepted solution]

@Panas_Wiwat-1964 

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 ?

[#3]

@Hans Vogelaar 

Still as soon as I did that the data on the other worksheets disappeared.

[#4]

@Hans Vogelaar 

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.

[#5]

@Panas_Wiwat-1964 

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.

[#6]

@Hans Vogelaar 

Thanks, I hope so too.  It's very frustrating.  Was the problem not there in Windows?

[#7]

@Panas_Wiwat-1964 Opened your file on an older MB Air (Big Sur, MS365 subscription, beta) The first few seconds the screen was white, then it opened properly. Although, it is "beach balling" (i.e. updating very slow) whenever I change something or switch to another sheet. But that's probably due my MB and the size of your file. Nothing disappears, though. See picture. Which Mac-OS are you on? Which Excel version?

[#8]

@Panas_Wiwat-1964 

No, I didn't notice a problem when editing the Master sheet. The data that were present on the other sheets before were still there.

[#9]

@Riny_van_Eekelen 

The data on the master sheet is fine, but the problems are in the daily sale & ad expense and the monthly sales sheet.  When I first open the file, all the data was there, but as soon as I put data in the master sheet, the summarized data from day 12 in the daily sales report sheet disappeared.  Very frustrated cause I need that data for our next sales meeting this Saturday.

[#10]

@Hans Vogelaar 

Maybe it's a Mac specific problem.  I am using Mac 10.15.7 and excel for Mac version 16.44

[#11]

@Panas_Wiwat-1964 I see. Tested your sheet again and noticed that the sheet doesn't calculate automatically, even when "auto calculation" is switched on. Copied all formulae from row 12 down and everything calculates instantly. Saved the file, closed it and reopened it. Now, when I add a row with data, the sheet calculates properly. Can't really explain why this happens, but I notice that your sheet has serious performance problems. But that's perhaps only an issue on less powerful systems (like my MB Air). And just trying to "play-around" with all the conditional formats hangs-up my Excel instantly.

Perhaps you need to consider collecting your transactional data without too much fancy formatting and use Pivot Tables to summarise it. You can achieve more-or-less the same results, cutting down on the file size and number of calculations and increasing performance considerably.

[#12]

@Panas_Wiwat-1964 

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.

https://stackoverflow.com/questions/46996846/maximum-number-of-conditional-formatting-rules-in-excel...

I deleted the format conditions in the attached file. Perhaps try opening the file and test to see if it runs any better? 

[#13]

@JMB17 

Yes, It does run faster, but the data in the other worksheet still doesn't show up properly.

[#14]

@JMB17 

For some reasons, I couldn't log in under the same name, so I had to register a new name.  Sorry, for the confusion.

[#15]

@Riny_van_Eekelen 

How do I do that?  A friend of mine created the whole worksheet for me.  I know nothing about creating conditional formatting.

[#16]

@JMB17 

I input new data but it still doesn't show up properly in the other worksheet (please see.)

[#17]

@PanasW56 Perhaps you can ask your friend to help you again to "clean-up" your sheets. But, I understand that removing the CF did speed up the sheet, but did not resolve the problem.

Attached workbook contains your data without formatting and two pivot tables to demonstrate what is possible. And this is just a very simple example.

[#18]

@Riny_van_Eekelen 

Thanks.  I am afraid she is not capable of doing the pivot table (she told me so).  Is there anyway I can pay someone to set it up for me?

[#19]

@PanasW56 I'm affraid that working for money goes beyond the scope of this forum, but since you are comfortable with the sheet your friend made and you don't seem to have much experience with Excel, perhaps better to continue with the attached sheet. All conditional formatting removed from the Master file. On a Mac myself (not a very powerful one), added an entry att the bottom and it showed up without problems in the summary sheets. What kind of Mac ar you on?

[#20]

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.