How to change GETPIVOTDATA formulas with one stroke

orehovka

New Member
Joined
Jan 31, 2014
Messages
7
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,
 

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.

XOR LX

Well-known Member
Joined
Jul 2, 2012
Messages
4,517
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
 

Watch MrExcel Video

Forum statistics

Threads
1,130,342
Messages
5,641,583
Members
417,223
Latest member
jelena_

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
Top