Pivot table value column name reference to cell with formula

As far as I know, you cannot rename field names in the pivot table with any formula or the named range.

Well there are ways to go about not as you have asked for. Could you please post a workbook that have the pivot and the cell you want to point as a filed ?
gowflow

I Will upload how I would like my pivot table to looks like and behave. It is not original file with data, hope that you can get idea what I want.
Test_Example_Pivot_Table_Value_Colu.xlsx

Please Please and Please  if you want a solution make our life easier. This file is useless. Please post a file with data and feel free to change the figures and sensitive data.
Gowflow

Hi gowflow I put some data in hope that this help to get clue what I want to achieve.
Test_Example_Pivot_Table_Value_Colu.xlsx

ok now that we have data can you please on the example state what you are trying to achieve or want to achieve as Sorry I do not see what you want (maybe am slow)
Gowflow

I on third sheet I put what I want to columns title look like:
Column that says This_Year_Month should be  2019
Column that say  Last_Year_Month Should be  2018

Column that says This_Year_3Months should be   2019
Column That says Last_Year_3Months should be  2018

Column that says This_Year_12Months Should be  2019.
Column that says Last_Year_12Monts  Should b3   2018.

The rest I know how to handle.

I am using this run date to pass to store procedure on SQL Server database and retrieve data in data sheet. So,  whichever date user enter as run date(parameter) title of those pivot table columns should reflect it.
I If he enters run date 09/21/2013 those two dates should be 2013 and 2012 instead those titles. I hope that it is more understandable now.

ok let me understand the functioning when u put the date does it filter the data ? coz in your pivot I see nothing that point to E3 ?
and also as yo want it this date should be in the pivot sheet not the data sheet right ?

You are posting half the story can you post the entire thing ? change the names I have no problem but I need to see when u change the date where you have your formulas that kick the results for that date ?

gowflow

ok I hadn' tread your post now it is more clear

EASY !!!!

When you send to sql the return fields should be

instead of This_Month_Year
February 2019

Last_Month_Year
February 2018

When building your
sql this is where you need to get the field name
Instead of This_Year_Month,Last_Year_Month …..
you do This_Year_Month AS Format(date,"mmmm") & " " & Year(date) etc...

where date is the date that your user input

gowflow

Why not change the headers of the data table after the data is imported from the SQL Server as per the run date so that when you insert the Pivot Table, you don't need to change the Field Names?

How to change the header of data table after the data is imported from SQL Server as per run date?

After data is imported, you may run a macro to change the headers of the imported data as per the run date and then insert the pivot table so that the pivot table will have the modified field names.

In your sample file, on Data Sheet, I assume E3 is the run date and row 5 is the header row for the imported data.
In Row 4, manually input the headers you would like to see as per the run date instead of the default headers and then upload the sample file again.

ok here is what I got for you.

I created 2 routines
ChangeHeaddings
ResetHeaddings

Basically what it will do if you click on ChangeHeaddings it will look for your pivot table datafields and change each one showing This with the year in E3 on sheet Data plus the last word in that Field ie

For the first one This_Year_Month  ---> will become  2019 Month (if date in E3 is say 2/18/2019)
Last_Year_Month ---> 2018 Month
This_Year_3Months ---> 2019 3Months
etc...

Why the Month, 3Months, 12Months first of all to know what data you are looking at and most importantly you cannot have like you requested 2019 for all the "This" as once you get the first field 2019 the next one you get an Error as Field already exist. Remember this is a table and you cannot have duplicate fields.

Now the second Macro is there for you to reset headdings as soon as you run it will will re-initialise your headdings back to what they were originally.

Try this workbook by changing the date in E3 then running ChangeHeaddings … look at results …. then Run ResetHeaddings then change again the date and run ChangeHeaddings etc...

Here are the 2 subs that have been incorporated in the attached macroenabled workbook.

Option Explicit

Sub ChangeHeaddings()
Dim WSd As Worksheet
Dim WSp As Worksheet
Dim I As Long
Dim pvtField As PivotField
Dim pvt As PivotTable
Dim sHeader As String
Dim vHeader As Variant, vParts As Variant

'---> Disable Events
With Application
    .EnableEvents = False
    .DisplayAlerts = False
    .ScreenUpdating = False
End With

'---> Set Variables
Set WSd = Sheets("Data")
Set WSp = Sheets("Pivot")

sHeader = "This_Year_Month ,Last_Year_Month ,This_Year_3Months ,Last_Year_3Months ,This_Year_12Months ,Last_Year_12Months "
vHeader = Split(sHeader, ",")
Set pvt = WSp.PivotTables(1)

'---> Change Header
For Each pvtField In pvt.DataFields
    For I = LBound(vHeader) To UBound(vHeader)
        If pvtField.Name = vHeader(I) Then
            vParts = Split(pvtField.Name, "_")
            If InStr(LCase(pvtField.Name), "this") <> 0 Then
                pvtField.Caption = Year(WSd.Range("E3")) & " " & vParts(2)
            Else
                pvtField.Caption = Year(WSd.Range("E3")) - 1 & " " & vParts(2)
            End If
            Exit For
        End If

    Next I
    
Next pvtField

'---> Refresh Pivot
For I = 1 To WSp.PivotTables.Count
    WSp.PivotTables(I).RefreshTable
Next I

'---> Enable Events
With Application
    .EnableEvents = True
    .DisplayAlerts = True
    .ScreenUpdating = True
End With

MsgBox "Fields changed successffully. Please check Pivot tables.", vbInformation, "Pivot Field Change"
End Sub

Sub ResetHeaddings()
Dim WSd As Worksheet
Dim WSp As Worksheet
Dim I As Long
Dim pvtField As PivotField
Dim pvt As PivotTable
Dim sHeader As String
Dim vHeader As Variant, vParts As Variant

'---> Disable Events
With Application
    .EnableEvents = False
    .DisplayAlerts = False
    .ScreenUpdating = False
End With

'---> Set Variables
Set WSd = Sheets("Data")
Set WSp = Sheets("Pivot")

sHeader = "This_Year_Month ,Last_Year_Month ,This_Year_3Months ,Last_Year_3Months ,This_Year_12Months ,Last_Year_12Months "
vHeader = Split(sHeader, ",")
Set pvt = WSp.PivotTables(1)

'---> Change Header
For Each pvtField In pvt.DataFields
    If IsNumeric(Left(pvtField.Name, 4)) Then
        pvtField.Caption = vHeader(I)
        I = I + 1
    End If
Next pvtField

'---> Refresh Pivot
For I = 1 To WSp.PivotTables.Count
    WSp.PivotTables(I).RefreshTable
Next I

'---> Enable Events
With Application
    .EnableEvents = True
    .DisplayAlerts = True
    .ScreenUpdating = True
End With

MsgBox "Fields changed successffully. Please check Pivot tables.", vbInformation, "Pivot Field Change"
End Sub

Select allOpen in new window

Try it and let me know if this was what you were looking for.
Regards
Gowflow
Test_Example_Pivot_Table_Value_Colu.xlsm

growflow when I tried your suggestion to replace that at time when I am building my SQL I tried and got error.
  Select
  a1.Shop_Name
  ,a1.Supplier_Name
  ,a1.This_Year_Month  as Format(@Start_Date,'MMMM') & ' ' & Year(@Start_Date)
  from CTE_A1 a1

error was incorrect syntax near '@Start_Date' . Expecting '(' or Select

Have you tried the last solution I posted ? Please forget about my first suggestion was a rough not tested solution. Try the one I just posted and let me know.

Regards
Gowflow

That solution look to complicated for user he wants to enter date and refresh no clicking and running macros.

Problem with the same column name in pivot as 2019  I can avoid adding empty space after 2019 or 2018 and pivot table will accept it.
I like your suggestion to work around first it in sql statement and get column titles out as close much as I what. Then later in pivot table I can  adjust a little bit that names with empty space after.
 Please if you can help me with that error not sure why it is there, variable @Start_date is declared as Date in my store procedure and accepts date passed from excel without problems.

HOLD ON !!!!
Your jumping the gun too fast. Please try my solution and then I can automate this thru the SQL routine like nothing and your user will need to do

NOTHING !!!!

I cannot suggest SQL unless you post the whole macro that generate the SQL then I can adapt.

But frankly speaking this one is much neater and you simply need to put 1 line at the end of your SQL macro to call this one.OK for the year I will patch with blanks and remove the Months, 3Months … that bugs you no sweat.

Just please post the SQL Macro will take it from there.

Let me know
gowflow

Hire is my script, this is from my store procedure
SQL_Script_Source_Data.txt

OK but meanwhile checkout this version, much neater and did as close as possible of the worksheet you posted. Let me know if the format is fine. Then will worry how to integrate.
Just run ChangeHeaddings and to reset Run ResetHeaddings
gowflow
Test_Example_Pivot_Table_Label-V02.xlsm

growflow I run it and it looks good.

gowflow.
It runs very well and it is doing what I need, gowflow thank you for you help and patient.

My client wants to move their reports from Crystal Reports to excel as is more convenient for them to run them through company as excel files and share them like that. Excel being part of Ms Office is more available and chipper than Crystal report and is easier to combine with other application. So that said it  asks for some strange adjustment  as excel is different presentation tool than cr. However from accounting point and  it is very good tool.
Thank you very much.

Your welcome I am glad I was able to get you what you want.
Gowflow