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!!

Once you have entered the PO data and the files have been updated, is that data likely to change? If not, then you can fix the values so the formulae are no longer active, and thus will not slow your file down. You can always add a routine to re-apply the formulae and then fix the values if you suspect that some details might have been changed retrospectively.

Hope this helps.

Pete

The PO data is entered into individual workbooks. Right now, we have over 500 of those. Each one, after being created is permanently static.

The only workbook which is dynamic is the Log file. It need to update on command, when opened, as each new PO Form is created, which should be daily.

Yes, but if you are about to enter PO number 501, you don't need to have active formulae for the previous 500 POs.

Pete

I understand what you're saying. But I guess I don't have an idea of how to implement that idea.

Thank you again for your patience and help on this.

So essentially remove the dynamic ability of the Automated PO Log?

Why do you need it to be dynamic if the historic data is not going to change? You only fix the values for the POs that you have entered - the last one can still have the formulae applied which will make it easier to copy the row of formulae down for future POs to be entered.

Pete