How to repeat a single value across Pivot Table columns

[synchysis]

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.

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

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:

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

 

[GlennUK]

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.

 

[synchysis]

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?

 

[GlennUK]

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.