How to change formula automatically when copying down 1 row.

[CharlieWR]

Could anyone help with how I can change date automatically in the formula below when I copy this formula down in excel.

=GETPIVOTDATA(" Total sales",'5.Rev-cost food'!$A$1,"Date",DATE(2023,6,24))

At present i have to change the date manually each day. Next d5)ate above would be

DATE(2023,6,25))

thanks

[#2]

Hi @CharlieWR 

One way:

=GETPIVOTDATA(" Total sales", '5.Rev-cost food'!$A$1, "Date", DATE(2023,6,23)+ROWS($1:1))

it's really DATE(2023,6,23)

[#3]

@CharlieWR 

Most probably PivotTable has Date, like

If so formula could be

=GETPIVOTDATA("Total Sales",'5.Rev-cost food'!$A$1,"date",'5.Rev-cost food'!$A2)

and drag it down

[#4 · Accepted solution]

Thanks for the solution I used this and found it does a great job

[#5]

@CharlieWR , you are welcome