Adding all cells across multiple sheets with certain formula.

[enanthate]

Hi all.

What I am trying to do is sum all cells across all sheets that contain a certain formuls.

I am looking at the time difference used in my favor or their favor.. Here is an explination.

I am using the E, F and G columns.

E is the time I ordered something, F is the time it was delivered.

G is the time difference showing either;
In my favor: =(E(x)-F(x))
In their favor: =(F(x)-E(x))

I have roughly 45 sheets in this document, where all have used E,F and G the exact same way.

How can I set up a formula on an "end sheet" to sum all sheets with =(E(x)-F(x)) and in another cell to sum all fields with =(F(x)-E(x)) ?

That way I can see the totals for time in my vs time in their favor.

It is in hours and minutes if that is important..

Thank you in advance!

[Greg M]

Hi there,

Life (i.e. Excel life) always gets complicated when negative time values are involved

Take a look at the attached workbook and see if it gets you moving in the right direction. A different approach could be used if your worksheet can accept helper columns, but I haven't tried that as I don't know your overall worksheet layout.

The workbook uses the following code:

Please Login or Register  to view this content.

The highlighted values can be altered to suit your workbook/worksheet layout requirements.


The problem was tricky but interesting!

Hope this helps - please let me know how you get on.

Regards,

Greg M

[Greg M]

Hi again,

The attached workbook shows an alternative method which uses helper columns I and J, and less complicated VBA code - the helper columns may be hidden if required.

The code involved is as follows:

Please Login or Register  to view this content.

The highlighted values can be altered to suit your own requirements.


Hope this helps - please let me know how you get on.

Regards,

Greg M