Program Formulas in Cells

[Questionz]

I normally avert from macros for several reasons but I'm wondering if it is capable of helping me in this case. I have document that takes a long time to open and process even the smallest change. Is it possible and would it be more efficient to program formulas into the necessary cells via macro? If so, what kind of strategy would one use to go about programing formulas into specific cells?

For practicallities sake, I have attached a small portion of what the main sheet that has all the formulas. Can someone please lead me in a direction of how to go about populating this LOB sheet. Thank you.

-The sheet is called LOB. The formulas here pull data from the other sheets in the workbook
-There are 3 items listed, however there will be about 3500 items in the final version.
-The cells I'm considering programming are highlighted in light blue. These cell formulas don't change; they are the same for every item on the list. I just use a script to paste them down the whole sheet for the amount of items needed.
-The LOB sheet has conditional formatting, etc.
-I'm not sure but the formulas that might be slowing things down the most might be the sumproduct formula that is in row 5.


Any help/thoughts/direction is appreciated.

[FDibbins]

Your file didnt come through

However, I have a feeling you can probably speed things up just by using more efficient formulas

[Questionz]

Here is a smaller sized doc so that if someone adds to it, it hopefully won't exceed the 1 MB file limit

[Questionz]

Hi FDibbins. Thanks for responding. The file came through now.

I'm all ears if you have any ideas of how to replace the current sumproduct formula.

[Questionz]

perhaps I can end this thread and find a different way to ask my question and provide a better sample.

thank you to any who read my question.

[JeteMc]

Have you considered changing the Excel Options > Formulas from automatic to manual. That could allow you to make changes to the document without Excel attempting to constantly evaluate all of the formulas. When you have finished making your changes press the F9 key so those evaluations will take place before the workbook is saved.
Let us know if you have any questions.

[MrShorty]

What I see in LOB is a lot of conditional sums trying to summarize the "database" contained in the other sheets -- which tends to make me think of a pivot table of some kind. Your source data is not really arranged in a nice database, so it is not "ready" to easily apply a pivot table to. I am not very good with pivot tables myself, but I would suggest you look into the possibility (especially if this is a long term need to keep track of and summarize this data frequently and reliably) of getting your raw data stored in a nice database (whether in Excel or other database program) so that you can summarize and query the data using pre-programmed database tools.

Even if a pivot table does not give you the exact final table you need/want, a pivot table can do the "slow" step of conditionally summing/counting/averaging/etc. very fast (compared to formulas and VBA). Then you can pull the data for your final table from the pivot table(s).

My thoughts on this, and it is probably worth about what you paid for it.