Pivot-table invoice issue

[niallstyles]

Dear All,

I'm having some trouble with a work spreadsheet (attached). I'm pretty sure there's a way of creating a specific type pivot-table from the first tab which would make manual entry/manipulation of data on the second tab of the sheet redundant.

Currently, I enter operative, job number, and working hours on the first tab. There are different rates in operation up to 37.5 hours and above 37.5 hours, I have a table which works out what rate needs to be applied and total invoice price etc. on the first tab, however, this is not the issue. The client requires a submission with two tabs, the second tab needs to include two near duplicate tables to the first one, however, normal hours are split into one table and overtime hours are split into another table. If there is a job that is spread over both conditions i.e. starts at 36 hours and ends at 42 hours, it would be split into 1.5 hours in the normal column and 4.5 hours in the overtime column.

Currently I do this all manually, I enter all the data into tab 1, then copy it into tab 2, and split it down into either table. I'm pretty sure that this can all be automated but can't work out how to manipulate the pivot-table to do this.

It would be much appreciated if someone could let me know if this is definitely possible, and if it's not too cheeky, if you could maybe give me a few pointers on how to do it too!

Any feedback is welcome,

Thanks,
Niall

[DonkeyOte]

Can you elaborate in terms of:

a) is the Invoice sheet fixed in terms of size (limited to 12 rows)

b) are there always two people on each Invoice each of whom are treated individually in terms of the RT/OT split ?

It would be useful in your sample file to set up the 2nd sheet based on the data in the 1st - as is the two are seemingly unrelated so although the logic would appear obvious the desired final layout of O/T section is not.

On an aside you can avoid double evaluating the SUMIFs on your invoice sheet by using MIN / MAX as appropriate:

Please Login or Register  to view this content.

So K will be the lower of 37.5 and total hours and L will be higher of 0 and O/T Hours.