My Index & Indirect Spreadsheet Log has grown too big and is now taking forever to calc.

What I've got is a Purchase Order (PO) System that I put together for my work some time ago with excel.

It's designed so that whenever our Project Managers make any new purchases, they inform our estimator of all the details for each purchase. The estimator then takes that data and enters it into individual workbooks by performing a "save as" off the most recent workbook (PO). I should note that each individual PO is formatted exactly the same.

I have also created another separate workbook which I refer to as the "Purchase Order Log". It's job is to gather all the data from each PO Form, dynamically, as they are created. This Purchase Order Log is intended to do as it says, log all purchases in one spreadsheet for ease of sort-ability and viewing.

The communication between all these files through this process has been working flawlessly for months now. Our estimator creates new PO's, and then our PO Log updates accordingly, all by itself.

But now to my problem. There are now over 500 individual PO's from which the PO Log is pulling data from. And this number is absolutely going to increase over time. Because of this, the calculation time to "re-calculate" all this data is getting increasingly longer. It's now to the point, that it's taking 10-15 minutes to update. I've researched numerous forums and topics in an attempt to troubleshoot a solution to "whatever" the issue is that is causing this extended calculation time. I've come to the conclusion that it has to do with how I've used either the INDEX, or INDIRECT (or combination of both) functions in my PO Log workbooks' formulas.

My hope is that somebody here can help me figure out a way to get my PO Log to calculate much faster. Especially as it is inevitable that as time goes on, it is only going to be pulling data from even more individual PO Forms.

I've went ahead and attached the both workbooks that we are using in hopes that one of you out there can help me figure this out. All formulas and functions exist in the PO Log file. Take note that in Column A, the formula is using the Name Manager to reference to locate the PO Form file from which to make this whole thing work. You will definitely need to update the 'FN' variable there. As for Columns B thru H, you will notice that I replaced the file location with a dummy one so that you guys won't know our top secret server names and file locations, muahahhahaha. Therefore, you will need to be sure to update that as well. It should also be noted that without column A's formula working, then column B thru H won't work either, as B thru H need A.

Although, whoever is smart enough to help me out with this will obviously be able to figure all that out on their own.

In conclusion, I hope that I wasn't too wordy, but just wordy enough. And... I greatly appreciate any help/ideas I can get on this. Also, I am leaving work for the day, so odds are I won't be able to see any reply's until tomorrow morning, which, for me, is 16 hours from the time of this post.

Again, I Thank You Greatly!!