How do I view Pivot Table source data file and field names?

I'd like information on how you can view the name/reference for the source 
data of a Pivot Table.  For Example, in MS Access and Cognos Impromptu you 
can go to SQL view and see where data in a Query is coming from.  It's easy 
to find the source of formulas in Excel, using the Audit Toolbar.  I'd like a 
way to quickly view the file name (whether an Excel file or external source) 
and field names that were used to create the Pivot Table.
0
9/14/2005 3:31:02 PM
excel.misc 78881 articles. 5 followers. Follow

4 Replies
414 Views

Similar Articles

[PageSpeed] 5

chocolate2346 Wrote: 
> I'd like information on how you can view the name/reference for the
> source
> data of a Pivot Table.  For Example, in MS Access and Cognos Impromptu
> you
> can go to SQL view and see where data in a Query is coming from.  It's
> easy
> to find the source of formulas in Excel, using the Audit Toolbar.  I'd
> like a
> way to quickly view the file name (whether an Excel file or external
> source)
> and field names that were used to create the Pivot Table.

Hi chocolate2346

Try this, right click on the Pivot Table, click Wizard, a dialogue box
will appear, click the Back button, a second dialogue box will appear
that shows the source of the data


-- 
Paul Sheppard


------------------------------------------------------------------------
Paul Sheppard's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=24783
View this thread: http://www.excelforum.com/showthread.php?threadid=467550

0
9/14/2005 4:09:20 PM
No, I'm sorry but the suggestion does not give me what I'm looking for.  For 
example, the Pivot Tables that I'm currently working with are created from 
queries in MS Access databases.  When I click the back button as suggested, 
below, I get the Get Data button.  When I click the Get Data button, I get a 
list of "Available Tables and Columns".  I cannot see what the name of the 
..mdb file is that the Tables and Columns/Fields are coming from.  That window 
also shows a list of "Columns in your query".  However, it does not identify 
the Table or Query that each column came from.  

"Paul Sheppard" wrote:
 
> Try this, right click on the Pivot Table, click Wizard, a dialogue box
> will appear, click the Back button, a second dialogue box will appear
> that shows the source of the data
> 
> 
> -- 
> Paul Sheppard
> 
> 
> ------------------------------------------------------------------------
> Paul Sheppard's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=24783
> View this thread: http://www.excelforum.com/showthread.php?threadid=467550
> 
> 
0
9/14/2005 5:37:03 PM
You can use programming to print the commandtext:

Sub PrintCommandText()
   Debug.Print ActiveWorkbook.PivotCaches(1).CommandText
End Sub


chocolate2346 wrote:
> I'd like information on how you can view the name/reference for the source 
> data of a Pivot Table.  For Example, in MS Access and Cognos Impromptu you 
> can go to SQL view and see where data in a Query is coming from.  It's easy 
> to find the source of formulas in Excel, using the Audit Toolbar.  I'd like a 
> way to quickly view the file name (whether an Excel file or external source) 
> and field names that were used to create the Pivot Table.


-- 
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

0
dsd1 (5911)
9/14/2005 5:50:24 PM
I found it!!

Go to Tools, Macro, Microsoft Script Editor.  Do a search (Ctrl + F) for 
QuerySource.  It takes me to just what I'm looking for.  It shows the Name of 
the file and the Query/Table and Fields.

Hooray!

"chocolate2346" wrote:

> I'd like information on how you can view the name/reference for the source 
> data of a Pivot Table.  For Example, in MS Access and Cognos Impromptu you 
> can go to SQL view and see where data in a Query is coming from.  It's easy 
> to find the source of formulas in Excel, using the Audit Toolbar.  I'd like a 
> way to quickly view the file name (whether an Excel file or external source) 
> and field names that were used to create the Pivot Table.
0
9/14/2005 5:57:02 PM
Reply:

Similar Artilces:

Mail Messages HTML Viewing
I am using win XP and Office 2002. When I receive messages with html format i can only see the url's and not the pictures. I have checked all the settings, I have the box in options/mail format/internet settings/html format checked to receive pictures and still just the url. I runn norton and zone alarm but can't see any settings in these that would restrict the downloading of pictures. Any thoughts? What are your IE settings? --� Milly Staples [MVP - Outlook] Post all replies to the group to keep the discussion intact. Due to the Swen virus, all e-mails sent to my actual...

manipulated a data file in excel
Is there any way to have a button in excel where when I click on it, i will let me choose a data file (by pointing and clicking) that's comm delimited and I can simply sum up an array of numbers and import tha sum into a cell? I don't want to put the text file into th spreadsheet, but rather just sum of the numers I want into a specifi cell. Thanks -- skinnydud ----------------------------------------------------------------------- skinnydude's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1627 View this thread: http://www.excelforum.com/showthread.php...

MS excell 2007 Data verification list
I am attempting to use the list function within data verification for drop down menus. I need a list that is conditional upon the information in another cell. Example: The first cell offers a drop down menu with specific Make of equipment. From what appears in that cell I seek to offer only the Models for that Make of equipment on a list in another cell. I considered using a series of IF statements to run macros however I don’t know how to invoke a macro from within a formula. Any suggestions? Thank You =if(“Trane”,macro,=If(Crrier,macro,=If(Bitzer,macro…))) See Debra D...

the webpage you are viewing is trying to close the window
We have an internal web interface for our helpdesk system. When we finish working with a "call" it attempts to unlock the call and we get the message as above. More often than not, we need to say YES to the message. Any ideas how to get it to not appear? -- Keithmeister Hi, Which security zone does the page/popup load into? Hint: Is IE configured to automatically detect sites in the Intranet zone? What 'call' software are you using? Have you attempted to contact the vendor? Regards. "Keithmeister" <Keithmeister@discussions.microsoft.co...

password & 01 data
Hi, I bought an oem product on e-bay & can't get the free tech. support. Any assistance appreciated. I installed 04 & money asks for a password. Beats the heck out of me. No recollection of using password before in 01. Tried all kinds of e-mails, passport, .net, etc. No go. Need assistance. Also, once in 04 how do I access my 01 data. I downloaded 04 trial of the ms site. Never could open the 01 data. Thanks in advance, John. ...

can't open file #2
I created publisher 2000 files on a windows98 SE COMPUTER. I saved the files to disk. Just got a new Vista computer. I installed the publisher 2000 on the new computer. The disk shows the files but publisher says it cannot open them. Any hints? thanks -- joy artisan lampwork beads and jewelry http://wildorchidpotteryandart.com Try copying the saved files from the disk to the hard drive on your new computer and opening them from there. Bruce "wildorchid" wrote: > I created publisher 2000 files on a windows98 SE COMPUTER. I saved the files > to disk. Just got a new Vis...

Using custom document peroperties to creat views in Public Folders
How do I use custom document properties to create views in public folders? I came across an article for Outlook 2000. I tried doing this for Outlook 2007 but I am unable to. Please assist. The article I came across was http://office.microsoft.com/en-us/publisher/HA010549541033.aspx However this only appears to work for 2000 not 2007. Hopefully someone can help. "Whoadieson" wrote: > How do I use custom document properties to create views in public folders? I > came across an article for Outlook 2000. I tried doing this for Outlook 2007 > but I am unable t...

Excel table is not correctly pasted into PPT and LN
Hi, i would like to haves ome info about this problem: One user cannot paste correctly an excel table (Edit Copy > Paste Special/Image - Paste) from excel 2000 to powerpoint 2000 or lotus notes vs. 4.6 This problem does not exist in word or in onother excel sheet. Coloured cells are right, coloured text is pasted in black. Have you got some ideas to resolve the problem? Thank in advance, Sara (Italy) ...

m_pDocument Doc/View unique id for each view
I'm using the standard mfc mainform with a doc/view. While I'm in the view portion I need to track the actions taken individually and separately for each view. So when when I click on view one I track that in a separate place than view two. I there a unique id or index for each view? Something like m_pDocument->ID? Thank you, Joshua <jtfaulk@eudoramail.com> wrote in message news:1111085780.370621.117620@f14g2000cwb.googlegroups.com... > I'm using the standard mfc mainform with a doc/view. While I'm in the > view portion I need to track the actions taken...

File:// Linking...
Why is it that when you link something in Outlook with file:// when clicking on the link it returns the error Outlook 2000: "Cannot find the file 'file://Q:\blahblahblah' (or one of its components). Make sure the path and filename are correct and that all required libraries are available." Outlook 2003 "Cannot find the file "Q:\blahblahblah'. Make sure the path or Internet address is correct." It used to work before any service releases to Outlook 2000 were applied. There are typically no spaces in the filename and if they are I usually replace th...

Mailbox management Deail report data
I find that the output of the mailbox management detail reporting has a little too much detail for my liking. I would like to see a list of the folders containing old/big mail. I really don't care about all of the folders with no items that qualify for my criteria. Is there any way to get at that information that does not involve me parsing the text report? The data is there. It's just not in a very usable form. It a perfect world I'd love to use crystal reports to query exchange and do some of my own reporting. Got any ideas? -Steve ...

How to add a configuration file to a MFC project?
hi, I want to add a configuration file to a MFC project ,how an i get that? and I have a command like the following line: copy app.config $(ConfigurationName)\$(TargetFileName).config Where do I need to put? Any ideas would be appreciated! -ja Yes, I already know how to do it,thanks a lot! -ja "Ajay Kalra" <ajaykalra@yahoo.com> д����Ϣ����:%23ymi%23P%23yGHA.4176@TK2MSFTNGP06.phx.gbl... > What does it mean? Do you want to perform copying as part of build > process? > In VS, you can do so in Pre-Build, Pre-Link and Post-Build steps. You can > specify the...

How to calculate of % in Pivto table?
HI ALL, Could you please provide the details on how to calculate % throu Pivot Table? I Have an excel-sheet that contains a columns as Priority (High, Low, Medium). Using Pivot table, I added the column Priority in Colunmn Area and Data area. It is showing as numbers. I wanted to have a separate column that shows the % of a grand-total (as a separate column). How to do it? Thanks, ManInRed I don't know that you can display it as a separate *column*, but it can be displayed as a separate *row*... Drag the Priority field from the Pivot Table toolbar to the Data Area for a sec...

Consoldating Data
I have a spreadsheet with 57 columns of data. I'd like to figure out a quick way to have all the data (text) into just one column. I don't want add or multiply anything... just want all the data in 1 column. Hi Pierre You can use a function like this one Copy the function in a normal module =Rangecat(A1:BE1," ") Use this in the worksheet From J.E. McGimpsey It is working for rows an columns Public Function RangeCat(rng As Excel.Range, _ Optional delimiter As String = "", _ Optional direction As Integer = ...

View Webpage
How can i view a HTML page in Excel, it should also be automatically update in 60 seconds. Is this possible? Please note that, that page contains images That webpage is on our intranet website. Regards, Murtaza Though I am not certain this is exactly what you are looking for, try the following: Data, Import External Data, then New Web Query... In the dialog box that appears, browse the site. You will notice that at certain areas of the page you will see an arrow. Just click the ones that you want to import. Click import. Select the cell you want to import the data into and click OK. Hope...

New hotfix for event error
A number of users have reported this error in their CRM application event logs where it says cannot clean up activitybase table. Microsoft now has a hot fix for this issue as per this article TechKnowledge Microsoft CRM Deletion Service Failure Error "Cannot Clean Up ActivityBase Table" in the Application Event Log on the Microsoft CRM Server Return to previous page Document ID: 30380 Date Created: 4/24/2003 Date Last Modified: 12/8/2003 3:56:45 PM Language: English - United States C...

Forms in two differant views
Is there a way to use the same sub form showing two differant views within the same Main Form. For example: Can I show on one tab a datasheet view and on the other a regular form view. -- Rose Hi Rose, Create a button on your form and under the "OnClick" event put 'Me.DefaultView = Datasheet Alternatively you could use a checkbox If me.checkbox1 = 0 then Me.DefaultView = 2 ' Datasheet Else Me.DefaultView = 0 'Single Form End if me.repaint This should flick the form between datasheet and form views. HTH, Nick. "Rose" wrote: > Is there a way to use t...

Column Header that moves with Data?
Does anyone know if it is possible, and if so how to have a colum header move with the data? I have a sheet with a ton of data and the list is growing, I would lik to see the column header "float" with the list instead of having t scroll to the top and check it out. thanks for all help -- Message posted from http://www.ExcelForum.com Hi if i'm understanding correctly - click in cell A2 and choose windows / freeze panes to undo it choose window / unfreeze panes Cheers JulieD "wendellarina >" <<wendellarina.19p7jr@excelforum-nospam.com> wrote in mess...

Forcing a view to be the active view
Hello! I have an SDI application that has a splitter window with two views. The first view is an CFormView and the second is a CView. I want the CFormView to always be the Active view. Even if the user selects the CView. How do I accomplish this? // Anders Anders Eriksson wrote: > Hello! > > I have an SDI application that has a splitter window with two views. The > first view is an CFormView and the second is a CView. I want the CFormView > to always be the Active view. Even if the user selects the CView. > > How do I accomplish this? > > // Anders I'd ...

Multiple series not plotting in their respective data ranges
I have a price comparison chart (line chart) that has multiple prices for multiple dates. Price 1 has a date range of 01/01/07 - 02/06/07. Price 2 has a date range of 01/19/07 - 01/31/07 (a subset of the Price 1 range). When Price 2 plots, it starts at the left of the overall range (01/01/07) and stops at 01/13/07 instead of plotting in the middle of the chart starting at 01/19/07 and ending at 01/31/07. Is this a bug or is there a parameter setting I need to tweak? Thanks, Mike If you want to plot one variable (price) as a function of another variable (date), you should be using...

Data Lookup or query?
I have two fields (text boxes). Text box 1 is a Location and always has data inputed. Text box 2 is an Sub Location but only sometimes gets data inputed. I would like to be able to pull up that if Text box 1 is being filled in that prior data that matches pulls up and also if there is any matching data for Text box 2 that it would get filled in autmatically too. I guess almost like a combo box where it will recognize from a query or whatever, but then also automatically fill in the next field (text box 2)??? You could change the text box 1 to a combo that has both the value for text box 1 ...

Dynamic Pivot
I'm trying to create a pivot statement, however, the columns are dynamic in the sense that one month one of the columns could be used and the next it's not... Does anyone know of any good links to handle this situation. Thanks in advance Toby (Toby@discussions.microsoft.com) writes: > I'm trying to create a pivot statement, however, the columns are dynamic > in the sense that one month one of the columns could be used and the > next it's not... Does anyone know of any good links to handle this > situation. I have some information on http://www.s...

Coustom view
Hi, I have been given a new work station and for some reason my custom view is not highlighted and is not working. I don't think it is as add-in feature. Excel heip has not been of any (help). Does someone know how to enable this feature? Thanks, Michael Michael Custom Views will be disabled if you have more than one sheet selected. Do you see [group] behind the filename on title bar? If so, right-click on a tab and "ungroup sheets". Gord Dibben MS Excel MVP On Wed, 11 Jan 2006 06:28:03 -0800, "Michael" <Michael@discussions.microsoft.com> wrote: >...

missing font names
My system got infected and after cleaning out the virus, the font name drop down listed font names as a, b, c, ...z and defaulted to symbol fonts when i typed. Fonts in all other applications worked fine. This was true for every user account on my system. I tried uninstall/reinstall of ms word a few times and some other suggestions i found on the web, like deleting Normal.dot, deleting all restore points, etc. Nothing worked. Then, I created a new user profile (account) on my system. Logged in under that account and ms word worked fine. So, i think something was hosed in the hidden fold...

Data File Problem
I just switched from Quicken 2005 to Money 2005 and I was just starting to like Money when the following happened. I had money 2005 running smoothly when I needed to re-load windows on my PC. My Money 2005 data file resides on a Microsoft 2003 server, where it can be automatically backed up. After formating and re-installing Windows XP I proceded to install Money 2005. After the Money 2005 install I tried to access my Money data and I get the following error message. Money cannot locate 'X:\Users\BobG\Money\my money.mny" or cannot open it, possibly because it is a read-only fi...