We help IT Professionals succeed at work.

Pivot table value column name reference to cell with formula

Taras
Taras asked
on
63 Views
Last Modified: 2019-04-09
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)?
Comment
Watch Question

Subodh Tiwari (Neeraj)Excel & VBA Expert
CERTIFIED EXPERT
Most Valuable Expert 2018
Awarded 2015

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

Commented:
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

Author

Commented:
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
gowflowPartner
CERTIFIED EXPERT

Commented:
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

Author

Commented:
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
gowflowPartner
CERTIFIED EXPERT

Commented:
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

Author

Commented:
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.

Author

Commented:
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.
gowflowPartner
CERTIFIED EXPERT

Commented:
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
gowflowPartner
CERTIFIED EXPERT

Commented:
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)Excel & VBA Expert
CERTIFIED EXPERT
Most Valuable Expert 2018
Awarded 2015

Commented:
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?

Author

Commented:
How to change the header of data table after the data is imported from SQL Server as per run date?
Subodh Tiwari (Neeraj)Excel & VBA Expert
CERTIFIED EXPERT
Most Valuable Expert 2018
Awarded 2015

Commented:
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.
gowflowPartner
CERTIFIED EXPERT

Commented:
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

Author

Commented:
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
gowflowPartner
CERTIFIED EXPERT

Commented:
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

Author

Commented:
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.
gowflowPartner
CERTIFIED EXPERT

Commented:
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

Author

Commented:
Hire is my script, this is from my store procedure
SQL_Script_Source_Data.txt
gowflowPartner
CERTIFIED EXPERT

Commented:
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

Author

Commented:
growflow I run it and it looks good.
Partner
CERTIFIED EXPERT
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Author

Commented:
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.
gowflowPartner
CERTIFIED EXPERT

Commented:
Your welcome I am glad I was able to get you what you want.
Gowflow
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.