Strange pivot table request


New Member
Sep 10, 2010
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....


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

Latest member