How to change GETPIVOTDATA formulas with one stroke

[orehovka]

Hello,
I have several columns populated with GETPIVOTDATA formulas. Each formula refers to a different item, but they are all organized by months. Now I need to have the exactly same formulas applied to the next column, but showing the next month, e.g. instead part of my formula to be "Month",DATE(2014,8,1)" I want it to show "Month",DATE(2014,9,1)". Is there a way to change the month in all custom formulas with one stroke?

Thanks and regards,

 

[XOR LX]

Hi,

Is the one containing DATE(2014,8,1) the very first of your formulas? Or do you perhaps have one starting at e.g. DATE(2014,1,1)?

Also, what about the years? Do they need to roll over as well, i.e. to 2015 at some point?

And are you copying these formulas down, or across?

If your first formula references DATE(2014,1,1), then you can change it to either:

DATE(2014,COLUMNS($A:A),1)

if you're copying across, or:

DATE(2014,ROWS($1:1),1)

if you're copying down.

If you actually want the process to begin with a version for DATE(2014,8,1), then simply use this:

DATE(2014,COLUMNS($A:A)+7,1)

or:

DATE(2014,ROWS($1:1)+7,1)

Regards

 

[orehovka]

Thank you, I'll try this. Sorry for the belated response.