Array Formula


I am using an Index match formula in an array to extract my data. The problem is when I copy it down 6k rows, it takes FOREVER to calculate. 

This is sensitive data, so I will send it to the email that you have provided for such cases. 

If you look at workbook "Suspense" in cell c2 you will see the formula I am using "Insert" to pull the policy number in column A that matches the account numbers in columns C1 (2412100), d1(2416300), and f1(2203400) - This is my workbook I use

I do not need them summed, I just need what corresponds to the policy number and that specific account with the $dollar amount in workbook "Athene Suspense DB Aging" Sheet "Aging Detail" - This is the workbook that is published daily. 

The array formula works but the range is maybe too large (after I finally get the formula to work - Figures!) - Is there another formula or maybe even a VBA code that would work much faster because this array takes 5-10 minutes to calculate at least. 

***Workbooks attached in email****



Selected Answer

Have you tried turning off worksheet calculation while copying your formulas?

On the Ribbon's Formulas tab, select Manual from the Calculation Options dropdown. After copying the formulas you can then click Calculate Now (for the entire workbook) or Calculate Sheet and see how long it takes to bring the workbook up todate. Remember to set calculation back to Automatic when you are done tinkering.



Thank you so, so much!!
Sroncey21 (rep: 62) Dec 21, '18 at 1:10 pm
Add to Discussion

Answer the Question

You must create an account to use the forum. Create an Account or Login