Pivot table logic without the pivot table

**ccernat** · 07-24-2011, 03:50 AM

Hello all,

I'm new to this forum and new to Excel too. I prepared a small xls file as an example of what I want to have. I have 4 columns: FPO No, ITEM No, Operation No and Time. Now, for each different FPO No i have multiple operations like WELDING, BENDING, CUTTING, LASER, but some of the operations are repeating for the same FPO No with different times. I want that for each different FPO No to have the sum of each operation.

You will see in the xls better, please pardon my english.

Thx a million for any help.

LE: I just saw that there is a special New User section, my bad. If a moderator would be kind to move this thread. Thanks.

**Roger Govier** · 07-24-2011, 04:10 AM

Hi

Welcome to the forum.

I don't understand why you say you want Pivot Table logic, but without the Pivot Table.

In the attached file, I made your data into a List.
Data>List>Create List>my list has headers

This means that the list will automatically grow in dimensions as you add more data.
I then created a PT based upon this List, and set it up as shown to produce what you requested. I changed the format of the time cells to a custom format of [hh]:mm

I have shown 2 alternative layouts, one with the headings going down the page, and one across the page.

**ccernat** · 07-24-2011, 04:32 AM

Hello and thanks for your reply,

It looks great, but i cannot use it because you cant edit a pivot table. All my data will be taken auto from a database, then will be sorted in Excel with the help of formulas ( in wich I am a complete noob ) to be used as a production planning sheet wich should look like the following ( see the new atachment ). So i need the prod manager to operate on each production order individually ( color the cell with the hours he thinks he can do it in the week 04 lets say ) and then move on to the next one, etc. But for that I need the sum for one instance of each operation for each diff production order ( FPO No. ). And when the prod manager will hit refresh on the 2 worksheets that get the data from the database the worksheet with prod. planning will keep the already planned rows ( the hours per week per FPO ) and will update the new FPOs put in the system. Dunno if anything i said makes sense, i will attach the xls to see.

Thanks alot again.

**Roger Govier** · 07-24-2011, 05:54 AM

Hi

Not sure if I am understanding you correctly, and your data is linked to another file (no attached) so it is difficult to know exactly what values are being picked up.

However, I have created some dynamic named ranges (listed for you on sheet Summary) and then used Sumproduct formulae to pick up the hours projected in columns J:B1 against each FPO number.

=SUMPRODUCT((Productie=Summary!$A2)*(Operatie=Summary!B$1)*Hours)

**ccernat** · 07-24-2011, 08:51 AM

Hello,

I attached back your first example of Pivot Table.xls with additional explication, if you have some time to look on it will be great. Thanks alot for your interventions so far.

**Roger Govier** · 07-24-2011, 11:55 AM

Hi

I don't know whether this helps.
Clearly I haven't added all columns to the PT, but they could be, and you could click the dropdown on Data on the PT to choose the range of weeks you wanted to view.

I added a column at the beginning of the Data to show Completed, which can be Yes, No or Blank and added that to the Page are of the Pivot Table.

If in the Page area you chose Blank, then the Completed FPO's would not show.
If you selected Completed, then only the Completed ones would appear, and (All) would show you everything.

On your Data table itself, using the dropdown for FPO, you can select any order and fill in the expected hours against the various weeks, equally you could fill in Yes in the Completed column against those entries.

Selecting (All) again, would show all data.

I have also pushe the data down by a couple of rows, and added the Subtotal function in row 2, so, when you select any FPO, row 2 will also show the the totals for just that job.

I hope that this helps.