make a pivot from tasks per week

I have a sheet (activities per employee) where I have planned "workhours" per "employee" per "activity".

I would like to make a pivot table that shows per "week" the "name of the employee", its "activity" and the "time that need/will be spend" on that activity.

I guess I have to make a table in between. (see sheet 1 as example) Is that true? And if that is true, can I automatize that instead of a very error prone copy and paste work? Also, when I have to add an activity later on.

I have added an example

your help is very much appreciated.

your second source file doesn't exist so most of the values are: #REF!

that is correct, but that info is not really needed for this question (so far as I can assess) I only would like to show that there are formula's in these cells.

Working with #ref!s is very annoying,

good luck

do not want to violate any rule here. I have changed the sheet. no Ref present anymore in the sheet

Yes pretty much. Wow thanks so far!

going to check your VBA code. for sure I will have additional questions.

Dear Oeldere,

I'm do not understand a lot about VBA. The only thing i can is recording a macro


First a question about how to run the code.

if you run the code the table disappears and the columns (year) and (week nr) that is correct?
Would it be possible to keep the table as before running the macro.

You created a table with 2 rows of headers, how do you do that. I would like to copy that to my original sheet. the header is important for the formula's in each week.

IF(AND(((DATE(K$1;1;-2)-WEEKDAY(DATE(K$1;1;3))+(Table12[[#Headers];[40]]+1)*7))>Table12[@[start date]:[start date]];Table12[@[end date]:[end date]]>((DATE(K$1;1;-2)-WEEKDAY(DATE(K$1;1;3))+(Table12[[#Headers];[40]]+1)*7-4)));Table12[@[total needed hours]:[total needed hours]]/((Table12[@[end date]:[end date]]-Table12[@[start date]:[start date]])/7);"")

I would like to play a bit with your code but until now I did not succeed in copying it to my origin sheet. I guess it has something to do with the columns in between the activity,name and the week numbers which are in my original sheet.

Is it possible to help me with the lines I have to change to adjust it to my original one? Which is more ore less the same as the one I posted. Actually, the one I posted the first time, was the original. But that was found annoying and i did not want to irritate anybody.

Dear Oeldere,

Try to explain myselve a bit better. If you run the Macro the table layout dissapear and the colunm F and G dissapear. It is not a big problem to overcome this but if possible I would like to keep the layout. Only the data should be changed.

Yes, is really does what I want. It makes a row for each "activity" , "name" and "week combination" (with content) That is really impressive and nice!!

yes the pivot is perfect. I only filtered the zero's and the blanks out of it by adding "value" into the report value.

1. indeed, i would like to have in column F and G the cod if possible.
2. You created a table with 2 rows of headers, how do you do that? I would like to copy that to my original sheet. the header is important for the formula's in each week.
3. I would like to play a bit with your code but until now I did not succeed in copying it to my origin sheet. I guess it has something to do with the columns in between the activity,name and the week numbers which are in my original sheet.
Is it possible to help me with the lines I have to change to adjust it to my original one? Which is more ore less the same as the one I posted. Actually, the one I posted the first time, was the original. But that was found annoying and i did not want to irritate anybody.

Dear Oeldere,

first of all I would like to thank you for your support and patience. Many thanks.

I added the sheet which is exactly the same at the one I would like to use (origin)

I do not get a failure in your sheet. The point is that the table disappear after running the code. So not a nice formatted table anymore. Only a the data with the headers above. Absolutely not a problem, the only thing that would be nice is to have the formula (for F and G) inside the code
I get a failure in my origin sheet.: When I copy the code in my origin, it does not have the same columns as in the example you used for the code. So I (with your help if possible) need to adjust the code so that the right columns are used.
See attachment, if you can make the code in this attachment I will be helped completely.

Regards

i have tried to copy everything (my orignial data) into the sheet you created. Once I add extra columns it mess up the data completely.

I i coppy your code into my original sheet, it only gives the headers but there is not data sorted at all. ( I have added the extra row 3.)

It is possible for you to adjust it in attachment at #17?