How to repeat a single value across Pivot Table columns

synchysis

New Member
Joined
Mar 29, 2020
Messages
8
Office Version
  1. 365
Platform
  1. Windows
Hello lovely people.

I have searched and searched for an answer to this, but maybe I'm lacking the appropriate keywords... either way I'm stuck.

I have created the below pivot table, which shows the number of monthly support hours subscribed to by a client (1. Regular), any additional hours they purchase (2. Added) and the amount of hours of work done by my team (3. Billable and 4. Non-billable). I need the values for "1. Regular" to be copied over to the following month forever until superseded by a new value.

i.e. in the example below, "Benno Test Company" usually subscribes to 20 support hours a month, but in March, they dropped that to 15.

1585475426639.png


Here is a "hand drawn" version of how I would like this pivot table to look:

1585476286382.png


The requirement here is that a non-techie user should only have to input the value for "1. Regular" for each client ONCE, then that value is applied to each month until a new "1. Regular" value is added. Here's a snapshot of the data the pivot table uses, which is added on a different sheet in the same workbook:

1585476170624.png


So my only concern here is how "1. Regular" is handled. The rest is working fine! I spent a full day yesterday trying to find a solution. Anyone have any ideas? I'm up for anything as long as the end user only needs to input a value once.

Even some suggested search terms would be useful. I've been stuck in a loop searching for variants of "pivot table repeat value across columns" or "pivot table repeat value until superseded" or "pivot table create monthly constant value", I must be missing some terminology. Or is what I'm asking logically impossible?

Thanks,

Ben
 

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.
I think you would be better off using a pivottable to do all the summarizing, and then pull that data when necessary into a fixed layout report, and then you can fill in blank Regular entries with formula results.
 
Upvote 0
I think you would be better off using a pivottable to do all the summarizing, and then pull that data when necessary into a fixed layout report, and then you can fill in blank Regular entries with formula results.

Hi Glenn. Thank you for the reply! That's an angle I hadn't considered, I'll certainly give it a go. Did you mean something like the method described in this link?

I filtered my example above to a single test client, but I have some 130 clients to do this for. I was planning on making use of the pivot table's ability to insert page breaks between each item (to produce a report for each client) - do you know of a way to achieve this in a fixed layout?
 
Upvote 0
Hi Glenn. Thank you for the reply! That's an angle I hadn't considered, I'll certainly give it a go. Did you mean something like the method described in this link?

I filtered my example above to a single test client, but I have some 130 clients to do this for. I was planning on making use of the pivot table's ability to insert page breaks between each item (to produce a report for each client) - do you know of a way to achieve this in a fixed layout?
Oh boy, I see what you mean ... I would want to use PivotTable page breaking, and other facilities, too, if I had that data, and those requirements.

I have another idea ... and this would mean a 2 stage process ... stage one, create a PivotTable to summarise your data, but shaped to look like data (Design=Tabular, no subtotals, etc), and have formulas to the left of the PivotTable to mimic the PivotTable, BUT, fill in the missing Regular payments. Stage 2, make a new PivotTable from the formulas to the left of the PivotTable in stage one. That should give you all the data, and all the functionality.
 
Upvote 0

Forum statistics

Threads
1,213,539
Messages
6,114,221
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top