Strange pivot table request

[charleymax]

Hi all,

I have a protected and shared worksheet with thousands of rows of shared data in it showing a Week number work is scheduled for (numeric, 1-52), the project code (alpha) a document type (text) and the standard hours for that doc type (numeric).

To that sheet I've linked a separate unshared pivot table which takes the week numbers I put in column A and plots them by project number to give me Sum of Standard Hours....

What I now want to do is be allowed to put 2 or more weeks in column A and get my pivot table to recognise that I want to split that work over the 2 weeks I specify... so in col A I would put say 18,19 and my pivot table would then pick up that the standard hrs of say 6hrs are to be split 3hrs each for weeks 18 and 19.

I know I might have to have the entire sheet read by a macro, if Col A is one number the row copied to new sheet, if 2 numbers 2 new rows created and the std hrs split between the rows, if 3 numbers 3 rows and std hrs split between the 3 etc....

so

would rewrite to a new sheet as
see below reply...

Any pointers or suggestions would be greatly appreciated...

I know this is a tall order...

 

[charleymax]