Pivot Table displaying seperate values in one table?

**Zelles** · 02-01-2019, 10:56 AM

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

**teylyn** · 02-03-2019, 04:47 PM

Hello,

can you post a small sample of the source data, then mock up the desired result manually and explain the logic that leads to that result. With pivot tables, the data structure of the source matters. Post that structure.

**Zelles** · 02-04-2019, 10:38 AM

Sure I can :-) Thank you for your reply.

I appended the example-table. In the first table (source data), there are 3 companies (AAAA, BBBB and CCCC) and "Last Year".
They all have starting contracts and ending contracts in different calendarweeks of 2019 except for "Last Year", which are the contracts that started last year and are going to end in calendarweek 10.

What I want the pivot-Table to do is (as you can see on the second sheet) display the whole year in calendarweeks and arrange the starting and ending contracts into that display. But everytime I try that, the pivot-table only takes the values from the start-weeks and displays it twice - once in "starters" and once in "Stoppers", so that for example in calendarweek 3 for Company AAAA it would say starters 1, Stoppers 1 instead of the 1&2 it should display.

Is there some way to accomplish this? As I said, I guess it's got something to do with what I set the columns/rows/values to display. I just can't wrap my head around what option could theoretically lead to my desired result.

**teylyn** · 02-04-2019, 04:03 PM

Sorry, I was asking for the source data for the pivot table. You have posted some data that is separated by an empty column, so, clearly that is not the data you use for the pivot tables in your screenshot. Are they really pivot tables??

Where does the text "Starters" and "Stoppers" come from if it's not in your data source?

Maybe this is not a pivot table at all?

**Zelles** · 02-05-2019, 05:59 AM

I... I thought I was supposed to mock up the desired result manually, so I didn't make another non-functioning pivot table but rather built together a manual depiction of what I want the table to do. It thought that was what you wanted?
The source data is the same sheet I used in the examples in post 1. I don't know what to tell you. The source sheet in post 1 also had an empty column between "Start"- and "End-calendarweek". I merely deleted the "Filler"-Company because it's not really needed to show what I want.

I double clicked the "Start-calendarweek" and "End-calendarweek" cells and renamed them to increase visibility. I assure you that the tables in Post #1 are Pivot-Tables. They draw data from the source sheet and automatically use it to display what I don't want to see. That's the whole problem^^

I just want to combine both Tables in Screenshot two; the result should be a single, big table that shows starters and stoppers for each calendarweek, seperated by company. Is that possible or can pivot-tables only ever use a single value per table?

Thank you for putting up with me - maybe I just did not understand what you need to help me :-/

**teylyn** · 02-06-2019, 03:59 PM

To build your layout in a pivot table, your source data needs to be a flat table like in this screenshot:

2019-02-07_08-56-32.png

**Zelles** · 02-11-2019, 08:23 AM

Ahhh, thank you - problem solved :-D

Problem=User, I guess.

Pivot Table displaying seperate values in one table?

LinkBack

Thread Tools

Rate This Thread

Display

Pivot Table displaying seperate values in one table?

Re: Pivot Table displaying seperate values in one table?

Re: Pivot Table displaying seperate values in one table?

Re: Pivot Table displaying seperate values in one table?

Re: Pivot Table displaying seperate values in one table?

Re: Pivot Table displaying seperate values in one table?

Re: Pivot Table displaying seperate values in one table?

Thread Information

Users Browsing this Thread

Similar Threads

Creating a (pivot) like table displaying values not count

Displaying Specific Values in Pivot Table

PIVOT TABLE BUILDER not displaying values, only sum of values (mac 2011)

[SOLVED] Pivot table displaying ratio % values (20:80) from spreadsheet as '#DIV/0!' or '0'

Displaying a sorted table dependent on Data Validation or Pivot Table

pivot table from Power Pivot not displaying thousand separator/comma

Displaying more "Values" in pivot table

Bookmarks

Bookmarks

Posting Permissions