Avatar of Taras
TarasFlag for Canada asked on

Pivot table value column name reference to cell with formula

In excel 2013, Can you rename value field in pivot table with name that referencing a cell on another sheet (cell in which I have a formula)?
Microsoft ExcelMicrosoft Office

Avatar of undefined
Last Comment
gowflow

8/22/2022 - Mon
Subodh Tiwari (Neeraj)

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

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
ASKER
Taras

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
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
gowflow

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
ASKER
Taras

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
gowflow

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
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
ASKER
Taras

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.
ASKER
Taras

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.
gowflow

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
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
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
Subodh Tiwari (Neeraj)

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?
ASKER
Taras

How to change the header of data table after the data is imported from SQL Server as per run date?
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Subodh Tiwari (Neeraj)

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.
gowflow

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

Open 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
ASKER
Taras

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
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
gowflow

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
ASKER
Taras

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.
gowflow

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
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
ASKER
Taras

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

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
ASKER
Taras

growflow I run it and it looks good.
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
ASKER CERTIFIED SOLUTION
gowflow

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
ASKER
Taras

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.
gowflow

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