large amount of data requiring YTD and monthly charts from multiple sheets

Hi spenny32 and welcome.

This is a great example of why it's always better to keep data in one long list rather than split into separate ones!

There would be no need to filter the data for double checking purposes if you're formulas are written correctly in the first place, but if you do want that double checking possibility then you could use a pivot table for that. This would also give you the possibility of getting an instant monthly/weekly etc. version of the data by simply double clicking on the relevant number in the pivot table.

The single list would make your formulas more efficient and easily give you the ability to cut it in whichever way you so desired without the need to worry about weeks crossing over month end.

BSB

thank you BSB. I originally started with the YTD tab, just was unsure of my formulas at first (not sure if there are better ways of writing them) but they seem to be calculating correctly so i may just switch to a YTD tab only.

If i were to switch to a YTD tab, do you think it would be feasible to have all 8 offices data on one workbook? or would 20,000+ rows for each by the end of the year would make this too sluggish?

That all depends on which formulas you're using and if there are any extra like Conditional Formatting etc.

I regularly use workbooks with upward of 250k rows of data without much problem with formulas causing problems on it. As I said, all depends on which formulas you're using. Would need more info on that.

BSB

hmm... here is an example of one of the YTD formulas - it might be a simple one, although to me it looks complicated haha

=SUM(COUNTIFS('YTD2018 _PBL_Cycle_Time'!V:V,"<30",('YTD2018 _PBL_Cycle_Time'!G:G),"<>LOG",'YTD2018 _PBL_Cycle_Time'!F:F,{"6-A-S","7-A-S","8-AB-X","8-AB"}))

Count the instances of V that are less than 30, where G doesn't equal LOG and where F is either 6-A-S, 7-A-S, 8-AB-X or 8-AB.
Pretty straight forward to me

Your formula is referencing entire columns rather than just the rows you're using. That can cause a bit of slow down if you have lots and lots of formulas.

There are ways around that, such as referring to just over the maximum number of rows you'd need, or using dynamic named ranges to include only the rows that are being used.

BSB