Suggestion to find a subtotal for todays date only.

[JeffGrant]

Hi All,

I am look for some suggestions on how to best handle this scenario.
In my workbook, there are many sheets, and on a daily basis the data is these sheets grows dramatically.
One of the functions I perform is to record the the subtotal of todays figures on another sheet.
Because the contibuting data is getting very deep, using the subtotal function on each sheet is doing a lot of calculation for no reason, when i am looking for one figure, todays subtotal on each of the sheets.
I don't want to delete the contributing data because it used in other areas a well.

In a nutshell, I am looking for suggestions on how to calculate the subtotal for todays date only. I see that a possability could be to use the range.subtotal property, with the range being rows that include todays date.

Thanks for the suggestions in advance.

Jeff

 

[Joe4]

Have you considered using SUMIF or SUMIFS formulas, to only total the values from today's date?
You can use the TODAY() function in the criteria, so it will always dynamically choose the current date.

Note: Another thing to consider.
It really sounds like you have a relational database model. As such, these sort of things usually perform much better in a relational database program, like Microsoft Access or SQL.
They typically also handle large amounts of data much better than Excel does.

 

[JeffGrant]

Hi Joe4,

Of course. Thanks for the suggestion. All I can say is that sometimes, I am too close to the woods to see the trees . A fresh pair of eyes is always a great benefit of this forum.

As for the Relational database, sadly many of the formula i use in this workbook are either extremely difficult to replicate, which I dont havethe skills for or dont exist in database programs. I am sure they can be done with enugh money set aside for development though.

 

[Joe4]

Hi Joe4,

Of course. Thanks for the suggestion. All I can say is that sometimes, I am too close to the woods to see the trees . A fresh pair of eyes is always a great benefit of this forum.

As for the Relational database, sadly many of the formula i use in this workbook are either extremely difficult to replicate, which I dont havethe skills for or dont exist in database programs. I am sure they can be done with enugh money set aside for development though.

No problem. We have all been there.
Glad I was able to help!