Results 1 to 7 of 7
  1. #1
    Plutonium Lounger
    Join Date
    Nov 2001
    Posts
    10,550
    Thanks
    0
    Thanked 7 Times in 7 Posts

    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. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    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. #3
    Plutonium Lounger
    Join Date
    Nov 2001
    Posts
    10,550
    Thanks
    0
    Thanked 7 Times in 7 Posts

    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. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    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. #5
    Plutonium Lounger
    Join Date
    Nov 2001
    Posts
    10,550
    Thanks
    0
    Thanked 7 Times in 7 Posts

    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. #6
    Plutonium Lounger
    Join Date
    Nov 2001
    Posts
    10,550
    Thanks
    0
    Thanked 7 Times in 7 Posts

    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. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    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
  •