Originally Posted by
DCSwearingen
I have a lot of spreadsheets in which I summarize daily data into weekly, monthly, and YTD key indicators. I usually set up all of my data sheets to keep data in columns and set up for a Fiscal Year.
If there are 15 data numbers, this would equal 16 columns, the first being the date. I then keep a rolling week for each indicator (that's another 15 columns,) a MTD for each indicator (15 more,) and YTD (another 15.)
I have historically made heave use of VLOOKUP. But that requires me to actually have to open every file when I update the summary reports, as VLOOKUP and HLOOKUP are not suppose to refresh data unless the linked workbook is also open.
I recently came across the SUMPRODUCT function that is suppose to be able to refresh data without actually opening the linked workbook.
This last alone does save me some personal time in the updating.
With dozens of sheets feeding the summary report, and all of the individual cells having all of the LOOKUP or SUMPRODUCT functions, as well as all of the calculations done to get the rolling week, MTD, and YTD my individual file sizes have really grown since I started this process.
Does anyone have any advice as to how to minimize file size and speed up Excel when opening and updating?
Bookmarks