Forum Discussion

CharlieWR's avatar
CharlieWR
Copper Contributor
Jun 25, 2023
Solved

How to change formula automatically when copying down 1 row.

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

excel
Formulas and Functions
  • CharlieWR's avatar
    CharlieWR
    Jun 25, 2023
    Thanks for the solution I used this and found it does a great job
  • SergeiBaklan's avatar
    SergeiBaklan
    MVP
    Jun 25, 2023

    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

     

  • Lorenzo's avatar
    Lorenzo
    Silver Contributor
    Jun 25, 2023

    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)

Resources