recalculation of array formula while updating rows slows down excel

**shtuamouth** · 03-20-2014, 01:20 PM

Dear All,

I have a workbook containing 2 worksheets. One is JV (a Journal voucher template) and the other JournalE (contains the raw data).

There are about 18500 rows of transactions in the JournalE worksheet. I have used an array formula in this sheet in Column P to pick up all the data from Column B and to display it as a list using Data Validation in the JV worksheet against JV No. or cell F7.

While updating a few transactions is not a problem but if i continue to update and drag a single cell (in Column P of the JournalE worksheet) and apply it to the rest of the rows, as i move ahead the excel workbook hangs or crashes.

Kindly help me in this issue.

I have attached the dropbox link for the workbook may you test it for me. Thanks in advance.

https://www.dropbox.com/s/3c5kbcbmk3...st%20file.xlsx

**RobertMika** · 03-20-2014, 02:41 PM

There is no much you can do with 18000 rows.
If this is one of just filter the range for unique items or use VBA .

**MrShorty** · 03-20-2014, 02:45 PM

I'm not very good at this kind of spreadsheet programming, but it looks to me like a similar issue to the one benishiryo and I discussed some time ago: http://www.excelforum.com/excel-form...-and-cons.html

I haven't looked at your scenario in great detail, but it appears that, as in the previous discussion, each of your array formulas is computing the IF(LEN(...)) part, which will likely result in a lot of duplication of effort. By pulling these duplicated parts out and putting them into helper columns, you may be able to eliminate a lot of the duplication of effort and, thus, decrease the computation time.

**newdoverman** · 03-20-2014, 06:03 PM

The file is much to large to upload even zipped.

Instead of using a formula to get the unique values from column B, copy then paste values to L2 and Remove duplicates then sort in order. (you can record a macro to do this) With the range still selected, name it AllTxn.

In the Name Manager amend the definition of AllTxn to

Formula:

Please Login or Register  to view this content.

This creates a dynamic range named AllTxn.

Create a drop down list in F2 with =AllTxn as the definition formula.

Disable all your Array formulae and see the difference in performance.

On my laptop, it was dramatic.

I deleted a worksheet so that the file would be small enough to upload when zipped. You should get an idea of the above from the file.