lost_in_the_sauce
Board Regular
- Joined
- Jan 18, 2021
- Messages
- 128
- Office Version
- 365
- Platform
- Windows
Currently trying to improve upon company financial statements - the previous version is an all in 1 with all journal entries hardcoded in previous months and accounts only at rollup level.
I am trying to build 4 files to host in One Drive/Sharepoint to improve our internal process (version control, better drill down ability, etc).
File 1 is the master data paste from Great Plains that gets updated - due to growth there are lots of reconciliations still going on in previous months so there are 21,200 lines of data that all have an additional 9 columns of info mapped to them from a different tab based on identifier codes from GP. Size is 3 Mb.
File 2 is the Income Statement - 9 identical tabs for 8 brands + 1 consolidated that just adds the 8 tabs together in each cell, non-rollup cells are SUMIFS to the first file with 5 references for matching. 66 columns for months of history, not counting rollups or empty rows for formatting, 650 rows that pull mainly from file 1. 3 extra tabs for ancillary data, not a ton. Size is 5.6 Mb.
File 3 is the Balance Sheet, just like file 2, pulls from file 1, rows are down to 406, but each cell adds the one before due to aggregation. One row pulls from file 2 (Net Income). Size 4.4 mb.
File 4 Is the Statement of Cash Flows, and it honestly pulls from Files 2 and 3. It's the least complicated aside from the original data file, maybe less because not 21K lines of data.
Anyways - these things lock constantly. File 2 won't work with any numbers if File 1 isn't open. Saving takes 5+ minutes if I'm lucky enough to save and don't have to task manager -> end task. I'll only be eating ~250 Mb of RAM but power usage is Very High, almost no other apps running (I'll even close Teams, Chrome, and Outlook - no help at all). And this is just working on my computer - I had to uninstall One Drive because it kept trying to sync everything and just bringing my entire work computer to a complete halt.
Current computer is a Dell XPS 15 9510 - i9 2.5GHz, 32GM RAM, 64 bit, Windows 10 Business with Office 365.
I am trying to build 4 files to host in One Drive/Sharepoint to improve our internal process (version control, better drill down ability, etc).
File 1 is the master data paste from Great Plains that gets updated - due to growth there are lots of reconciliations still going on in previous months so there are 21,200 lines of data that all have an additional 9 columns of info mapped to them from a different tab based on identifier codes from GP. Size is 3 Mb.
File 2 is the Income Statement - 9 identical tabs for 8 brands + 1 consolidated that just adds the 8 tabs together in each cell, non-rollup cells are SUMIFS to the first file with 5 references for matching. 66 columns for months of history, not counting rollups or empty rows for formatting, 650 rows that pull mainly from file 1. 3 extra tabs for ancillary data, not a ton. Size is 5.6 Mb.
File 3 is the Balance Sheet, just like file 2, pulls from file 1, rows are down to 406, but each cell adds the one before due to aggregation. One row pulls from file 2 (Net Income). Size 4.4 mb.
File 4 Is the Statement of Cash Flows, and it honestly pulls from Files 2 and 3. It's the least complicated aside from the original data file, maybe less because not 21K lines of data.
Anyways - these things lock constantly. File 2 won't work with any numbers if File 1 isn't open. Saving takes 5+ minutes if I'm lucky enough to save and don't have to task manager -> end task. I'll only be eating ~250 Mb of RAM but power usage is Very High, almost no other apps running (I'll even close Teams, Chrome, and Outlook - no help at all). And this is just working on my computer - I had to uninstall One Drive because it kept trying to sync everything and just bringing my entire work computer to a complete halt.
Current computer is a Dell XPS 15 9510 - i9 2.5GHz, 32GM RAM, 64 bit, Windows 10 Business with Office 365.
Last edited:
