Nintendo Switch review: Hands-on with the intuitive modular console and its disappointing games…
I have a spreadsheet with 40 tabs. All the tabs are needed and most have formula linking to a central repository of data held in 1 of the tabs. Essentially it is 2 years worth of accounting data and I am presenting each division of the company in a separate tab (as requested by management).
I have been using named ranges and sum product, however, I have reached the point where Excel does not appear to calculate everything. After a calculation it still has the word calculate in the bottom left hand corner (this is with automatic calculation turned on). I would like to erduce the time taken to calculate and ensure that everything is calculated.
The biggest problem is that there may be more divisions to add soon too.
Does anyone know a less memory intensive formula I could use?
a complete alternative, albeit involving a lot more work in the short term, would be to reengineer the sheet. Are the 40 or so tabs identical in layout, but referencing different divisions from the central data? If so do they all need to be shown (and calculated) at the same time, or are they viewed one by one?
We had a similar issue, resolved by replacing multiple identical tabs (albeit with different departments in each) with one tab that was populated by the user choosing which department to view through a short piece of VB and various VLOOKUP and HLOOKUP formulas. As a result the file was much smaller and efficient.
VoG - I have thought of copy/paste special, but we revert back at the start of each year nad the user of the sheet is not excel literate.
bstb3 - I did have lookup on a pervious incarnation but wanted to get away from the looking up of rows and columns. The department tabs are identical, but we cannot show just one as they feed into a summary tab and other analysis data.
I have been told about Offset and Match functions but have never used them and dont know if they would be of use.
This thread is now locked and can not be replied to.