Complex array formula takes long time to return results

[holmes123]

I have a long, complex array formula that returns values from an Excel data table based on criteria that gets updated based on the user's selections. It's copied down about 3000 rows, which is the maximum number of values I can ever expect to return (the data table is periodically updated).

With 50 values to return, it takes a few seconds.
With 100, it takes a few more.
With 2,500, it takes up to a minute.

Any recommendations to speed up complex array formulas for this number of records?

Alternatively, because this formula isn't essential, is there a way to run just this particular formula *on command?* (Like a control button on the spreadsheet that says, "Click here to list all X records? This may take a while.")

Thank you.

[Paul]

Hi Holmes,

I would suggest posting the formula you're using, as well as a sample copy of your workbook. (Replace any sensitive data with fake information.) Perhaps there are better alternatives to your formula.

[olasa]

Keep the formulas in row 1, copy/paste down as values is one alternative.
Otherwise - as mentioned - more facts would help.
//Ola

[holmes123]

The workbook and formula has a lot of dependencies and will take a while to clean up, desensitize, etc. In the meantime, I've posted the formula and some details.

The array formula starts in B4 and is copied down for 3,000 rows. It looks in tblData (about 45K records) and extracts the OrderIDs, listing them from B4 to, potentially, B3000. It only extracts OrderIDs which match the user's criteria.

I'm using structured references to refer to the data table. The basic technique is to check if the value in tblData (e.g., tblData[OrderType]) matches the criteria, which is stored in a named range (e.g., valOrderType).

This is the formula, which I've attempted to annotate:

Please Login or Register  to view this content.

• cntOrders = the count of order numbers to be returned (the sum of another table that shows the number of orders by region)

Using combo boxes and worksheet change events, the user specifies what order details he wants to see. His criteria are assigned to these named ranges:

• valOrderType = Type of order ("Mail", "Phone", "Internet", etc.)
  valCriteria1Yes = Repeat customer (always has "Yes")
  valCriteria1No = Repeat customer (blank or "No")
  valCriteria2Yes = Happy customer (always has "Yes")
  valCriteria2No = Happy customer (blank or "No")

For those Yes and No criteria, the formula evaluates these with a +. If only "Yes" is selected, it returns only OrderIDs with a "Yes"; if "Yes" and "No" are selected, it returns OrderIDs with either "Yes" or "No".

[holmes123]

Keep the formulas in row 1, copy/paste down as values is one alternative.
Otherwise - as mentioned - more facts would help.
//Ola

I don't think I follow. If the formula is in Row 1, it will return the first value of the array. If I copy and paste down as values, it will simply copy and paste that first value over and over.