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

#### MckB

##### New Member
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

### Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.
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

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.

Replies
0
Views
212
Replies
8
Views
206
Replies
0
Views
303
Replies
2
Views
154
Replies
3
Views
255

1,207,111
Messages
6,076,607
Members
446,216
Latest member
BEEALTAIR

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back