Slow array-formulas

**Jonathan78** · 08-20-2009, 10:01 PM

Hello all,

I'm working on a sheet that has a lot of array-formulas, but they really slowdown my worksheet. Does any of you have a alterntive for this?

**Paul** · 08-20-2009, 10:49 PM

Hi Jonathan,

Honestly that's the formula I would use in that situation. Unless someone has an alternative, perhaps there's something you can do with your spreadsheet to improve performance, such as converting all previous dates to values instead of maintaining the array formulas.

You could even have a workbook_open macro run that looks for today's date in row 13 of sheet4, and automatically converts all previous used rows and columns to values.

**DonkeyOte** · 08-21-2009, 04:10 AM

One alternative might be:

Please Login or Register  to view this content.

**Palmetto** · 08-21-2009, 07:52 AM

I like the solution DO provided, but just to offer another alternative, you could add, in sheet1, column-E, a unqiue lookup value composed a join of employee and date and use an index/match formula on sheet4.

**Jonathan78** · 08-23-2009, 05:06 AM

Thank you for your help Paul, Palmetto and DonekyOte.
Sorry for my later reply. I'm new to this forum and just found out that my reply notification wasn't activated yet.

However I already found a solution similar to that of Palmetto.
It also needs an extra column where the "date" and "employee" are joined together. This is the formula I used to get it working.

Please Login or Register  to view this content.

The formula DonkeyOte came up with looks very promising, but I can't get it working.

**DonkeyOte** · 08-23-2009, 05:10 AM

If using helpers is viable option then that's the approach you should adopt is it is the most efficient available to you... (I would perhaps (as per Palmetto) advise using INDEX/MATCH in preference to VLOOKUP given the former will generate fewer precedents)

Attached is your sample file with the LOOKUP(2/1..) approach - I suspect the difficulties are down to regional settings (ie use of ; as delimiter as opposed to ,) - when the attached is opened it should auto-translate to reflect your own Dutch settings.

This approach is less efficient than the helper based method though obviously requires no helpers.