Stop calculating/freeze values on a changed cell and start calculating in a different cell

[Ingo ter Meulen]

Hello,

Happy New Year!
I'm new here and I've already tried to find a solution to my problem in this forum. Apologies, if I missed it possibly.

Scenario:

• 1 Pivot with Quarter to Date values (automatically gets updated by a report I pull)
• 3 tables: one per month (m1, m2, m3)

Normally within the first month, I just reference a value in the Pivot (e. g. "=F1"). Table m2 and m3 contain no formula at that time.

But when starting in month 2 I always have to copy/past values in m1, so values are not changing anymore and add a formula in table m2 to calculate the difference between Quarter to Date and table m1 (QTD-m1=m2).

In month 3 the same thing (copy/paste values in m2, then adding a formula for m3 (QTD-(m1+m2).

Is there some way to freeze the values in m1, when m2 starts and m2, when m3 starts? Thought first on simply putting an "X m1", "X m2" somewhere and to check somehow for this value, but I don't seem to get anywhere and I'm a bit tired of changing things around monthly.

Any creative solution? Would prefer one without lots of VBA, but if it turns out to be the only way...

Thanks
Ingo

PS: Btw, I'm using ASAP Utilities as well as Ablebits Ultimate for Excel, but nothing in there seems to help either.

 

[Roderick_E]

Why not a master sheet instead of m1 m2 m3, this way the pivot is only pivoting from one data source and you can easily just filter to desired qtr or month.
If you definitely need multiple sheets, maybe a little vba macro that generates these as needed but doesn't interact with the pivot.

 

[Ingo ter Meulen]

Thanks for your answer, but probably I didn't explain well enough.

There are 2 sheets, #1 containing the raw data, with only Quarter to date values (and a lot more other data ranging from column A:EB).
#2 contains 3 tables next to each other: m1, m2, m3 and the Pivot table pulling the Quarter to date values (revenue).

What I need is a way to handle the described within table #2 .
Can't change anything in #1 , as about 15 other sheets with lots of tables, Pivots and diagrams are depending on it.

Hope this helps to clarify.

Thanks for your help
Ingo