Forum Discussion
Cross sheet functionality and maintenance of the chronological order of a formula
Excel_Struggler The performance issue in this case is due to an array object (keyId) being passed to the lookup_array argument of XMATCH. If we spill the keyId results directly to a separate range in the worksheet, then reference the spill range in the lookup_array, it will drastically improve the calculation speed.
In the attached revision, I've defined the FILLDOWN function in Name Manager, moved the keyId formula to cell I6, and simplified the final formula in cell F6. I tested it to 16,000 rows and the calculation lag was virtually unnoticeable on my system (milliseconds). Hopefully it will perform well enough that you can avoid splitting the data into multiple sheets.
Cheers!
Hi djclements,
First and foremost, thank you for your detailed response.
Your solution and mine both function correctly in terms of retrieval order. However, I believe the explanation could have been clearer.
I tested your solutions, primarily the fill-down approach, given the inconsistent number of records in my workbook.
Unfortunately, with a sufficient number of rows, I still encounter performance issues using your solution, similar to what I experienced with mine. I tested around 8,000 rows.
I've attached a sample for you to test yourself.
Simply adding another column with data validation for the week is not a feasible solution for achieving the desired result in my workbook. My sample is highly simplified compared to my actual workbook, so it might give that impression. Assume a high number of rows in any case (about 8.000-15.000).
I'd love to hear if you have any other ideas to address my issue. I'm still inclined towards splitting the data into sub-sheets for simplicity and performance stability, but I'm unsure if this is technically feasible in Excel.
Cheers
Excel_Struggler The performance issue in this case is due to an array object (keyId) being passed to the lookup_array argument of XMATCH. If we spill the keyId results directly to a separate range in the worksheet, then reference the spill range in the lookup_array, it will drastically improve the calculation speed.
In the attached revision, I've defined the FILLDOWN function in Name Manager, moved the keyId formula to cell I6, and simplified the final formula in cell F6. I tested it to 16,000 rows and the calculation lag was virtually unnoticeable on my system (milliseconds). Hopefully it will perform well enough that you can avoid splitting the data into multiple sheets.
Cheers!
Wizardry!
The performance exceeded my expectations even with the additional formatting and formulas in my workbook.
Thank you so much for your assistance 🙂