Total number of pallets and times of each machine (chart)

[MckB]

Hi,

I've created a spreadsheet that is a production schedule.

It shows the finishing time and the amount of pallets for each job.

There are 12 machine so I've had to create 12 sheets.

Im trying to figure out if there is a way to show the total amount of pallets for each hour from all the machines.

E.g.

06.00 45 pallets
07.00 32 pallets
08.00 65 pallets

This way I can easily see if certain hours of the day are going to be overloaded and potential make some orders late. Therefore I will stop some pallets leaving the machine for the priority of orders.

Tried using a pivot table and a chart but don't quite seem to be working the way I want it too.

Also if this can be done automatically so I dont have to create a pivot table or chart each time that'll be great!!

Kind regards,

M

 

[BSALV]

12 machines = 12 sheets, that's the major problem, why can't you do it in 1.
Makes everything a lot easier, now and later.
machine10 6:01 12
machine4 7:12 2
etc

 

[Toadstool]

Hi MckB,

I agree with @BSALV that having all your data on one sheet, with a column identifying which machine, would make this much simpler. Excel has limited 3-D Functions (ones which will work across multiple sheets) and none of them are a lookup.

You could use SUM, as the example in that Microsoft link shows, but only if each of your 12 sheets was an identical format.

Another approach would be to designate a set area of each machine sheet (e.g. $ZZ$2:$ZZ$25) as the hourly production figure with a formula to retrieve totals from that same sheet. Then you could use the 3-D SUM on a Summary sheet to get the grand totals from those cells.

Another approach would be to have 12 columns with the first row being the names of each sheet. Below that would be the functions necessary to retrieve values for each sheet using the INDIRECT function.

Without seeing your sheets I couldn't give further advice.