×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

Microsoft: Office FAQ

PivotTable Tips

Find the original field name for a changed PivotField by SkipVought
Posted: 13 Nov 08

You have a PivotTable with a Source Data Table, either in another sheet or data from an external source, and you have changed one of the Field Names in the PivotTable, perfectly acceptable.

Now, you want to identify the Source Data Field Name for that Field Name you changed, and there seems to be no way to do that in Excel.

Paste this little function into a MODULE in the Visual Basic Editor.
How to:
1. alt+F11 (toggles between sheet and VB Editor)
2. ctr+R (displays the Project Explorer)
3. Insert > Module

Use as you would any spreadsheet function, on the sheet containing the PivotTable. Insert the function in a cell and reference the cell in the PivotTable with your changed Filed Name.

CODE

Function PivotFieldSouceName(PvtFieldName As Range)
'SkipVought 2008/11/12
    Dim pvt As PivotTable, PvtTableNbr As Integer
'first find the PivotTable
    PvtTableNbr = 1
    For Each pvt In ActiveSheet.PivotTables
        If Not Intersect(pvt.TableRange1, PvtFieldName) Is Nothing Then Exit For
        PvtTableNbr = PvtTableNbr + 1
    Next
'return the value
    PivotFieldSouceName = ActiveSheet.PivotTables(PvtTableNbr).PivotFields(PvtFieldName.Value).SourceName
End Function
 

Back to Microsoft: Office FAQ Index
Back to Microsoft: Office Forum

My Archive

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close