# Thread: Performance of Indirect (Excel 2003)

1. ## Performance of Indirect (Excel 2003)

This is the same workbook as <post:=644,978>post 644,978</post:>. I would prefer not to post the workbook if possible, as it would take a lot of effort to sanitise it.

Column C contains a list of email addresses, from which I have to select those I haven't written to recently.
Columns D, E, and F are used to work out whether this email address was used on each of the three previous surveys

Rows 2 to 16 of each sheet have stuff that isn't relevant to this problem
The headings of Columns D, E and F (in row 17) have the names of the previous 3 worksheets (I create a new sheet each time I use the workbook)
Row 1 of column D has the formula =""&D17&"'!c:h
Rows 19 onwards are used to check if the email address in column C appears on the sheet defined in D17 etc, with a "Yes" in columns H of that sheet
<code>=IF(ISNA(VLOOKUP(\$C18,INDIRECT(D\$1),6,FALSE) ),"No",VLOOKUP(\$C18,INDIRECT(D\$1),6,FALSE))</code>

This takes a very long time to recalculate and I would like to make it more efficient if I can.
When the sheet names were hard coded in columns D:F it worked fine, but was much harder to create a new sheet as I had to replace the sheet names in all the formulae.

StuartR

2. ## Re: Performance of Indirect (Excel 2003)

You wrote in your previous thread that you'd like to avoid using a macro, but wouldn't it be more efficient to use code to update the sheet name in the formulas? That way, youy wou;dn't have to use the INDIRECT function. INDIRECT is probably a major cause of the slowness.
You could even use code to populate columns D, E and F with values instead of formulas. I assume that these data will be static, so there would be no drawback to having values.

3. ## Re: Performance of Indirect (Excel 2003)

Hans,

I suspect you're right. I had been avoiding including code as it makes it much harder to share with other people - but I think the time is rapidly approaching.

StuartR

4. ## Re: Performance of Indirect (Excel 2003)

Will other people need to be able to add sheets too, or just use the workbook "as is"? If the latter, you could put the code in a separate workbook, and run it when needed. The workbook with the data would be free of macros.

5. ## Re: Performance of Indirect (Excel 2003)

This is a job that I just do myself at the moment, but once I have it sorted I want to be able to pass it on to one or more other people.

I suspect that the easiest solution will be to leave Auto Calculation turned off and tell them how to do a manual recalculate.

StuartR

6. ## Re: Performance of Indirect (Excel 2003)

I have gone a long way towards fixing my performance problem by...

Replacing the content of all Historical sheets with the current values (Copy, Paste Special > Values)
reducing the number of INDIRECT Vlookup functions by 50% by replacing code of the form
<code>=IF(ISNA(VLOOKUP(\$C41,INDIRECT(F\$1),6,FALSE) ),"No",VLOOKUP(\$C41,INDIRECT(F\$1),6,FALSE))</code>
I replaced this by putting the VLOOKUP... into E41 and then using IF(ISNA(E41),FALSE,E41)

I'm surprised this made so much difference, as I had assumed Excel would only calculate the intermediate value once, but this has got the recalculation time down from over 20 seconds to about 7 seconds, which I think I can live with.

Thanks for all the ideas.

StuartR

7. ## Re: Performance of Indirect (Excel 2003)

Good for you!

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•