Hey guys,
For work, I'm currently trying to stitch together a Pivot-Table in Google Sheets concerning start- and enddates of contracts and I've run into an impasse (see appended example). Since I tried the same Pivot-Table at home with MS-Excel 2016 and had the same errors, I know it's just my personal stupidity at play here, not the programs used.
I have two main problems with my table:
1. Is there some way to combine both tables and show starters and stoppers in every calendarweek in a single pivot table? Every time I've tried (and I tried a lot these past days >_<), it always displayed the starters twice, once in the starters and once in the stoppers row/column, and did not take ending contracts into account at all. (See screenshots)
2. Is there a way to permanently bind the summarizing formula at the bottom to the sums of both tables? So that no matter how large both tables got the formula would still always aim at the right cells instead of being fixed to "B21+C8-C18", for example? (It does that with normal formulas, but somehow the summarizing formula ignores the growth of the pivot table when a new company is added in the source sheet and stays fixed to the same cell).
Keep in mind that google-sheets is quite a bit crappier than Google sheets, so I had to put in filler-dates to show all Calendarweeks of the year. Just disregard those rows.
I would be eternally grateful if someone could help me, cause god knows I've been breaking my brain into pieces trying to figure this stuff out. :-O
Source sheet:
Pivot-Example Source Material.JPG
Seperate (working) tables I want to combine into a single one:
Pivot-Example seperate tables.JPG
My 30th try of a combined table. As you can see, it shows the same value in both rows (starters and stoppers). How do I get it to display both starters and enders for every company for every calendarweek?
Pivot-Example combined table.JPG
Bookmarks