Trying to SUM a constantly changing schedule.

**josebaptista** · 06-13-2013, 12:26 PM

Hello All,

I am attempting to create a schedule system for my workplace. (Just a quick visual representation)

I have calculated a 'Time per unit', and with this calculated a 'production hours' needed to complete the orders.
I update the orders on the 'Production' sheet, and with a simple macro it updates the information from this sheet onto the 'Scheduler' sheet.
From here all the times for the individual orders are then automatically calculated.

The problem I am having is,
I want to have a running SUM of a total weeks production.
I am having difficulty since the rows from the 'Production' sheet are always changing, which in turn is changing my range and cell position.
How would you go about solving this issue?

I have attached my workbook to show what I am talking about.

Thanks in advance,
Jose

**josebaptista** · 06-13-2013, 01:29 PM

Having a lot of difficulty with this, so all and any suggestions are completely welcome.

**tmk221** · 06-13-2013, 01:50 PM

Could you please clarify what exactly you want to sum? Do you want to sum columns I and J in Scheduler sheet for each of the week, like an example in cell K144, and then there should be another sum in cell K163 and then another one in cell K188 etc.? If yes then probably I could help.

**josebaptista** · 06-13-2013, 02:04 PM

Yes exactly!
I want to SUM the column totals for Production Hours (I) and Set-Up/Changeover (J) each said week.

This way you can add orders on the 'Production' Sheet, and the 'Scheduler' Sheet will SUM and add in the new order to the total production (in K144, K163, K188, etc.)

**tmk221** · 06-13-2013, 02:15 PM

Is column B going to be filled in with Customer all the time like in the example? That would help

**josebaptista** · 06-13-2013, 02:17 PM

Yes, it will be set up exactly the same way.

**tmk221** · 06-13-2013, 02:43 PM

At the moment I need helper column to solve this. In N144 type =COUNTIF(B145:B50001,"*Week*"), and copy it down as far as you need. And then in column K144 insert =IF(COUNTIF(B143,"*Week*")=1,SUMIF(N:N,N144,I:I)+SUMIF(N:N,N144,J:J),"") and copy down as far as you need.

If you cannot accept a solution with a helper column, then please let me know, I'll try to figure it out

**josebaptista** · 06-17-2013, 06:49 AM

Helper column is not a problem. Since the Scheduler sheet is completely separate from the Production I can use whatever is needed to solve the problem.

Thank you very much tmk!