Jan 05 2021 02:17 AM
Jan 05 2021 02:17 AM
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
Jan 05 2021 04:57 AMSolution
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 ?
Jan 05 2021 05:08 AM
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.
Jan 05 2021 08:16 AM - edited Jan 05 2021 08:18 AM
@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?
Jan 05 2021 06:42 PM
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.
Jan 05 2021 11:36 PM - edited Jan 05 2021 11:36 PM
@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.
Jan 06 2021 12:22 AM
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?
Jan 07 2021 06:02 AM
@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.
Jan 07 2021 07:23 AM
@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?
Jan 07 2021 10:36 AM