Strange pivot table request

charleymax

New Member
Joined
Sep 10, 2010
Messages
39
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... :biggrin:
 
Last edited:

Some videos you may like

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.

Watch MrExcel Video

Forum statistics

Threads
1,109,462
Messages
5,528,942
Members
409,848
Latest member
Blomsten
Top