When to separate worksheets over multiple workbooks?

If you are using formulas to link your data from sheet to sheet then it will affect the speed with which calculations are made as your data and number of sheets increase, especially if you are also using named tables (list objects). They all eat memory and forces Excel to use unallocated memory to do the calculation as events occur on the sheets. The number of sheets, for practical purposes, does not matter so long as your systems has adequate memory and most systems made after 2005 should have plenty of memory. My advice wouold be to start using VBA or VBScript to do a lot of your math, linking and manipulation of data. That would free up the amount of memory required to hold formulas and execute them, expecially the volatile formulas that execute when you open a workbook or activate a sheet. You would have to be the judge of how much code you use to replace your formulas, because only you know the objectives of what your data is compiled and manipulated to produce.

From my perspective, I would put all data into 1 sheet, if possible - it keeps formulas more simple.

A couple of times, I have had 1 data file that had a huge amount of data and was slow to calc (minutes). I took most of the calcs and put them in another file that pretty much ref'd the data file. This sped the process up considerably