Ongoing Total Formula

[victoriapowers]

This post was also added to EXCELGURU.CA (https://www.excelguru.ca/forums/showthread.php?8863-Ongoing-Total-Formula&p=36456#post36456)

I am looking for help regarding an ongoing total formula

I have a workbook with multiple sheets, and additional sheets being added daily.

Cell B126 will always have a number in it. I am looking for cell B127 to have a formula for an ongoing total, for example:

Sheet 1 (named Day 1) has the number 20 in cell B126
Sheet 2 (named Day 2) has the number 20 in cell B126, I would like cell B127 to add the totals of B126 from Day 1 + Day 2
Sheet 3 (named Day 3) has the number 20 in cell B126, I would like cell B127 to add the totals of B126 from Day 1 + Day 2 + Day 3
and so on....

I realize the standard =SUM formula will not work, as it would only provide the total amount from every sheet, where as I am looking for a running total based on Day 1, Day 2, Day 3, etc.

Workbook attached (called Excel Guru)

Thank you in advance for your time.

 

[Gerald Higgins]

For sheet 2, the basic formula could be

=B126+Sheet1!B126

And you can expand as necessary, to

=B126+Sheet1!B126+Sheet2!B126
(this would go on sheet 3)

And so on.

If the sheets are all adjacent to each other, you can do something like this, which will help as the number of sheets increases . . .

=SUM(Sheet1:Sheet3!B126)

BUT, and it's a VERY BIG BUT - STOP AND THINK - do you REALLY NEED a separate sheet for each day ?

Maybe you THINK you need a separate sheet for each day, but for all kinds of reasons it will probably make life MUCH MUCH easier for you if you keep all your data on a single sheet, and then perhaps use a separate sheet to do reporting on whichever day or combination of days you are interested in, from time to time.

 

[Joe4]

Sounds like this file could end up being huge.
What you are describing is really a database. If at all possible, it would work much better to do something like this in a database program, like Microsoft Access, SQL, Oracle, etc.
That is what those programs are designed for!

 

[victoriapowers]

For sheet 2, the basic formula could be

=B126+Sheet1!B126

And you can expand as necessary, to

=B126+Sheet1!B126+Sheet2!B126
(this would go on sheet 3)

And so on.

If the sheets are all adjacent to each other, you can do something like this, which will help as the number of sheets increases . . .

=SUM(Sheet1:Sheet3!B126)

BUT, and it's a VERY BIG BUT - STOP AND THINK - do you REALLY NEED a separate sheet for each day ?

Maybe you THINK you need a separate sheet for each day, but for all kinds of reasons it will probably make life MUCH MUCH easier for you if you keep all your data on a single sheet, and then perhaps use a separate sheet to do reporting on whichever day or combination of days you are interested in, from time to time.

Thank you very much for your response. This workbook is for a construction company that needs to see reporting on a daily basis. I WISH I could make this smaller but the big boss wants to be able to see certain labor numbers met on each day. With our construction jobs lasting 60+ days, the recommended formula would just be too time consuming to set up. Do you have any other suggestions for a progressive total based on numbers from the same cell across multiple sheets? Again, very much appreciate your time.

 

[victoriapowers]

It certainly already is. If you'd like to take a look at the original file (and also it's hidden pages), feel free to hit the link provided on my original post. Any help would be greatly appreciated.

 

[Joe4]

It certainly already is. If you'd like to take a look at the original file (and also it's hidden pages), feel free to hit the link provided on my original post. Any help would be greatly appreciated.

I am unable to download any files from my current location, so I cannot see your file.

Converting it to a database program is no trivial task. It needs to be well thought out and designed properly (and if you do not have experience in that field, you may need to enlist the help of someone who does). However, it is well worth the endeavour, as it should be much easier to manage and use once it is up and running (when you use tools that were designed for that type of work!).

 

[victoriapowers]

I am unable to download any files from my current location, so I cannot see your file.

Converting it to a database program is no trivial task. It needs to be well thought out and designed properly (and if you do not have experience in that field, you may need to enlist the help of someone who does). However, it is well worth the endeavour, as it should be much easier to manage and use once it is up and running (when you use tools that were designed for that type of work!).

I appreciate the suggestion, truly Joe, but I'm working with a company that has been using Excel for daily logging for the past 10 years. I will attempt to bring up a new formatting program in our next ops meeting but until then, I'm tasked with this impossible task!

 

[Joe4]

I'm working with a company that has been using Excel for daily logging for the past 10 years. I will attempt to bring up a new formatting program in our next ops meeting but until then, I'm tasked with this impossible task!

Understood, that is often the case!

The tricky task is to try to convince the "powers that be" to use the right tool for the job. A fellow Moderator has a great quote. He said, "I suppose you could use a wrench to drive nails, but why would you want to when you can use a hammer?"