Excel 2003 Calculation Issue

  Admiral Allstar 14:51 21 Aug 2008


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?

  VoG II 15:10 21 Aug 2008

If some of the data is historical and won't change then I would copy those ranges and Paste Special > Values over them.

You can find some tips on calculation issues if you click here

  bstb3 15:21 21 Aug 2008

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.

  Admiral Allstar 16:09 21 Aug 2008

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.

Elsewhere on IDG sites

Xiaomi Mi Mix 2 review

What went wrong at the Designs of the Year 2017

iPhone X news: Release date, price, new features & specs

Comment utiliser Live Photos ?