Slow calculating in Excel 2003? What's the cause?

[Doofay]

Hello,

I've created a file in XL 2003 and then did most of the editing in 2007, now when I open it again in 2003 it takes ages recalculating the cells on one particular sheet, but it is fine if opened in 2007.

I'm trying to work out which element of the file could be causing the problem so I've listed what is going on in the file

The file contains the following:

1. 12 month sheets containing approx 2400 IF(ISERROR(VLOOKUP formulas on each sheet - that look up data on one central information sheet that holds approx 238 rows/18 columns of data.

2. The central information sheet has one column that has been defined as a named list, which is then used for data validation for a drop down list on the 12 monthly sheets.

3. One very simple macro that simply adds a new line on the information sheet.

4. One sheet that collates all the data from the 12 month sheets.

5. One sheet that has 3 pivot tables from the collated data in item 4.

The slow calculation problem seems to be when I click on the defined list on the central information sheet.

Is it that there are too many lookup formulas and 2007 can handle it and 2003 can't?

I've tried opening it on other PCs that don't have 2 versions installed in case there was some sort of conflict of having both installed and it still occurred.

I've tried getting rid of the different elements such as the defined list, macro etc but nothing seems to have sorted the issue.

Is there any known problems of XL elements that cause issues when switching from 2003 to 2007?

Obviously switching to manual calculation does sort it but the sheet is to be used by 12 people who don't have a lot of IT knowledge and would prefer to make it as easy as possible for the end-user.

The computer I am working off and the other comps I have trialled it on are all 6 months old or less and fairly high spec.

Thanks for any help or advice you can offer.

 

[RoryA]

VLOOKUPs are generally pretty inefficient, especially if you are returning multiple columns for a particular lookup value.
How is your named list setup? Is it a dynamic named range? If so, what formula do you use - OFFSET?

 

[Doofay]

Thanks for the response.

I'm new to named lists to be honest and haven't used them much. Somebody else set up the named list on this file, but I'm pretty sure that she did Insert/Name/Define. So I'm guessing not dynamic. What does it mean if it is dynamic?

Is there a more efficient method than VLOOKUP?

Could the addition of the ISERROR formula in all of the lookup cells be a large contributor to the inefficiency? Would a conditional format that turns the text in error cells white to hide the errors be more efficient?

There is no problem at all when the file runs in 2007, just in 2003.

 

[Doofay]

I forgot to mention that I'm having problems deleting the named list. I've been going to inster/name/define selecting the list and then delete and it's still showing as a defined list after I close the dialog box.