• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 198
  • Last Modified:

SImplified Formula to speed up calculations

Hello,

Can someone please have a look at my spreadsheet and let me know if there is a way to simplify my formulas on the ‘layout’ tab.
I wouldn’t usually mind but as my sheet grows, so does the calculation times.

I have attached a very basic example of what I have, except with the main sheet I am currently up to row 9565 on the ‘layout’ tab and it is becoming increasingly frustrating to try to work with.

Any suggestions will be greatly appreciated!!

example.xlsx
0
vegas86
Asked:
vegas86
1 Solution
 
byundtCommented:
INDIRECT is slowing down your spreadsheet recalc time because every formula containing it must be recomputed every time anything changes on any open worksheet.

If you want fast calculations, put all of your data on a single worksheet. You will need to add a column for the fiscal year. You can then produce your report with a simple SUMIFS formula like:
=SUMIFS(Data!E:E,Data!A:A,layout!$B4 & "-" & layout!$C4,Data!F:F,RIGHT(E$2,5))
This formula assumes that the fiscal year is in column F of worksheet Data. Columns A through E of worksheet data are the same as worksheet 05-06 and subsequent.
0
 
vegas86Author Commented:
Thank you so much Byundt! i just applied the formula and its working, if I could give you more points I would!
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now