Parent of item in Pivot table

Hello NG,

i got an Excel 07 pivot table with the dimensions Region, Country, City
I now want to get the "parents" of  a double-clicked item. Example

Europe
    UK
        London

If a users double-klicks on "London" i need to somehow get the "UK" and 
"Europe" value via VBA.
I catched the OnDoubleClick event of the worksheet and set cancel=true to 
avoid drilling down that normally would occur when double-clicking an item 
in a pivot, which works fine. I tried to adress the "parents" via 
ParentItem, but id did not succeed. Can anyone help?

Regards,

Jens 


0
Jens
5/21/2010 9:04:46 AM
excel 39879 articles. 2 followers. Follow

1 Replies
1466 Views

Similar Articles

[PageSpeed] 39

Excel 2007 PivotTable, PivotCell
Find associated PivotFields of cell.
http://c0718892.cdn.cloudfiles.rackspacecloud.com/05_21_10.xlsm
Pdf preview:
http://www.mediafire.com/file/n4zznd4zumr/05_21_10.pdf

0
Herbert
5/21/2010 7:03:56 PM
Reply:

Similar Artilces:

No unique index found for the referenced field of the primary table. (Error 3609)
Hi I am trying to create a relationship, using Tools >> Relationships, & keep getting this error message. I am trying to link table TM_CampaignCustomers with TM_CompContact. TM_CampaignCustomers has as its primary key field TM_CCID. TM_CompContact has PK_Contact. Bothe are defined as Autonumber, Indexed, No Duplicates. The join is from PK_Contact in TM_CompContact to FK_Contact in TM_CampaignCustomers. FK_Contact is defined as indexed - duplicates OK. When I tick the box 'Enforce Referential Integrity' in the Edit Relationships dialogue - I get this error message. Any idea...

Archiving Recurring Items #2
Using OL 2002 I need to Autoarchive past instances of a lot of recurring appointments. Apparently that's not designed to happen in OL. Is there a workaround? I thought of converting all the recurrences into individual appointments and using AUtoarchive on them, but there appears no easy way to do this either. Once I remove the recurrence, they all disappear. Any ideas? ...

SQL
It looks like it should work, and I have painstakenly looked over it for a long time. It runs, but the result is not what I want (checking if record exists): The primary key is the retort id & labor code & process_name & employee_name dim sqlcheck as string dim rs as dao.recordset dim db as dao.database set db = currentdb sqlcheck = "SELECT distinctrow Processing.Retort_id, Processing.labor_code, Processing.process_name, Processing.employee_name " & _ "FROM Processing WHERE (((Processing.Retort_id)=""" & Me.retortid.Value &...

Deleted Items #9
This concerns Outlook 2003: I have a user who cannot recover any deleted items from his Outlook. As emails are deleted, they appear to be permanently deleted. When you highlight the deleted items folder and go to tools|recover deleted items, there are no emails available for restoration. Am I missing a setting somewhere? TIA. Jim Hi Jim; First click on the deleted folder and check if there are any contents. if you see your mails, just drag and drop or highlight them using the shift key and mouse. then Move then to the Inbox. However, the deleted item box is empty, that means the...

Deleting All Rows for Duplicate Entries Except Those With Most Items In Row
I need to identify & delete rows of data. Most entries in Column B appear more than one time in my list, but the other data on the rows associated with these Column B duplicates are different. I need to keep just 1 unique occurrence of each Column B entry, and the occurrence I need to keep is the one that has the most items in each row. In the example below, I need to keep Rows 1 and 5 for the 2 unique entries in Column B. I am not comfortable with programs. Is there a way to accomplish my goal with formulas? A B C D E Row 1 KEEP 123456789 4567...

No attribute when adding parent customer to person
we see this message quite frequently. We add a new business, fill out all the data. Create a new person and when we add the company in the parent company field and save we get a no attribute error. Usually I will delete the person and recreate the person and it works. However this person has opened a new company and we do not want to lose the old links. Hi John, Can you provide the error message to us? or do you have access to the server event viewer? Do you see any errors there? If so can you please post the message here as well so we can help further. Thanks! Darren Liu, Microsof...

Conditional formating in pivot tables
Hi Is it possible to have conditional formatting on a field in a pivot table. I can apply conditional formatting to a range of cells, but if e.g. a wider range of data is produced the formatting doesn't grow with it. I don't want to have the 'Grand Total' columns have the same formatting as the rest of the sheet, but depending on what data is brought back, they're not necessarily in the same place. Am I making sense?! I'm using Excel 2002 Cheers Neil How many conditions will you have? If there's only one, you could check for "Grand Total" in the...

how to persist pivot chart data
I have two worksheets: one sheet to do scratch work, and one to contain the final product. I do the following: 1. Make a filter selection on the scratch sheet and copy the resulting pivot table and pivot chart to the final sheet. 2. Make another filter selection on the scratch seet and copy the resulting pivot table and pivot chart to the final sheet. ( I actually do many iterations of this.) Unfortunately, when I make another filter selection in the pivot table on the scratch sheet and copy the table to the final sheet, the first pivot chart on the final sheet reflects the results...

How do I compare output data from 2 pivot tables in a graph forma.
I have a pivot table created for 2004 that shows the number of inspections we received on a particular day of the week (ie. 5 on saturdays, 2 on wednesdays, etc.) I am creating the same pivot table for 2005 and am looking for a way to graph the data (bar graph) of the pivot table from 2004 to compare with my 2005 data (ie. last year we received 5 inspections on saturdays, this year we received xx amount on saturdays). Is this even possible to do? I am using Office Pro 2003 and any suggestions would be greatly accepted. Doobi, One option is to combine the pivot tables for 2004 and...

Producing combined table grouped by categories
Hi! I have two tables: Categories and Expenses. Categories table has 3 fields: CategoryID CategoryName CategoryDescription 1 Stationaries Office materials, pens, paper, etc. 2 Petrol Petrol for company vehicles 3 Vegetables Expenses related to vegetables 4 Fruits Expenses related to fruits 5 Utilities Gas, electricity, water, etc Expenses table has 4 fields: ExpenseID CategoryID ExpenseName AmmountSpent 4 ...

Remote control of tabls
I have a database tables on the back end and multiple versions of front end users. Is there a way to access the table from a remote location though the internet or upload the tables to the internet and have the front end access the information in a way that the updated tables will be available to all? Thanks -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access-tablesdbdesign/201002/1 There are a number of possible technologies to enable remote connections VPN CITRIX .... but this is the type of question that should be asked to your...

pivot tables #33
Thank for helping me whith this. I nned to create a calculated fiel where ia can divide a value in a row by the total of the value column. sonething like this. +A35/A100 where A100 is the total of the colum. So the result will be a porcentage. This have to be a calculated field inside the pivor table. Thank for your help. -- MG Instead of a calculated field, you can add data fields to the pivot table. In the pivot table wizard, double-click the data field button Click the Options button For 'Show data as', select % of column. mariagloria wrote: > Thank for helping me w...

Creating new row in other table
Dear All, Please teach me, how to make a new row/list in other table by entering number in other table. Sample, In the table A and in the field "Details", I put the number 2 and it automatically creates two rows in table B. Can this be done in MS Access? Thank you for your help. Regards, Maulwy Why? What business need are you attempting to solve with this approach? It's generally considered not very good database design to be creating 'empty' rows in a second table. Why do you want empty rows? And if the number you enter in TableA is a 'limit&...

Editing more than 1 table at a time with SQL.
Say I have two tables in a DB called TABLE1, and TABLE2. TABLE1 [IDNUMBER] [FIRSTNAME] 1 Ryan 2 Jennifer 3 Monica TABLE2 [IDNUMBER] [LASTNAME] 1 Johnson 2 Snipes 3 Smith My question is would I be able to use SQL in code to Edit, Delete or Insert data to both tables in one string rather than one table at a time? For instance what if I want to change both tables with a [IDNUMBER] = 2 to a [IDNUMBER] = 4, OR change the ...

Remove a third party (PDF) toolbar item from Outlook's Untitled message toolbar
How does one remove a third party (Adobe PDF) toolbar item from Outlook's Untitled message toolbar permanently. Where is this info stored? sounds like you have Adobe Acrobat installed. Why not simply un-install it. Hope this is useful to you. Let us know. rms SBCM wrote: > How does one remove a third party (Adobe PDF) toolbar item > from Outlook's Untitled message toolbar permanently. > Where is this info stored? ...

Pivot: where does Excel store the data?
Hi all, when I copy&paste a pivot table into a blank workbook with only one sheet and then send this workbook to someone by e-mail, the recipient is still able to drill down into the data by double-clicking a cell in the pivot table. Excel displays a new worksheet containing the original data. Where does Excel store the data, and what would be the simplest way to avoid showing it? Example: the recipient may only see salary totals per department, not the salaries of individual employees. Thanks, Timo Timo, I'm not the expert on "where" Excel stores the data. But I do ...

Open a form in context from a multiple item form
I have a multiple item form based on a query. It is a list of customers along with the total invoice amount. Each customer is identified by a category AND the customer name. I have added a button at the end of each line to open a form that allows me to edit the customer and also display a list of invlices in a subform. No matter what I try, the form I'm trying to open always displays the same customer record. It will not display the customer of the row in which I clicked on the button. I'm sure that this can't be that difficult, I just can't crack it. I am a beginn...

Exmerge and Deleted Items Retension
Hello, When I use Exmerge on a mailbox does it recover Deleted Items? When a domain users leaves the company they sometimes toss all their e-mail into the Deleted Items bin and click "Empty". I create a profile in outlook to open their mailbox. I would then select the "Deleted Items" folder then "Tools" and "Recover Deleted Items". Then I would export the mailbox to a ..pst and hand the .pst to a manager or replacement. Can I do the same thing by using Exmerge without going through all those steps to recover deleted e-mail? Thanks On Mon, 22 Ma...

More than one Table
I have an Old table TABLE 1 in an application which has a check fields CHK1, CHK2 in it. I have recently upgraded the application so that the check field are now a series of records in a sub-form TABLE 2. QUESTION I want to increment through TABLE 1 one record at a time, then run a conditional statement to see if CHK1, CHK2 is yes in each respective check field and if it is I want to open TABLE 2 and add record to TABLE 2 so that reflects the respective CHKn. My question is can 2 recordsets be open simultaneously and if so how do I swap between the 2 so that my VBA applic...

Subtracting Two Tables
Is it possible to easily subtract two tables and place the results into another table? I have two 4x4 tables. The left corners are at A1 and F1. I would like the result to appear in the corner at A7. -- Wayne Watson (Watson Adventures, Prop., Nevada City, CA) (121.015 Deg. W, 39.262 Deg. N) GMT-8 hr std. time) Obz Site: 39� 15' 7" N, 121� 2' 32" W, 2700 feet Solid waste produced to generate electricity per person over a lifetime: Nuclear power -- 2 pounds in a coke can ...

Extracting common ids across 12 tables and assoc data
I have 12 tables named as: Sep06, Oct06, .. Aug07 (each table is ~ 30k lines) In each table there are 2 fields of interest: CINU, AUM (the 2 fields are named identically in all tables) CINU contains ids (alphanumerics). Ids are unique within each month's table, but each month's table may contain slightly different ids (some common ids, some different ids) AUM contains numbers: negatives, zeros, positives I would like to query out a col list of distinct CINU which is present across each of the 12 tables (ie the common ids) for which AUM is <> zero, and to extract the AUMs f...

External table is not in the expected format
I get this error while trying to run a DTS package built to import an excel file. After some reading I'm thinking that the excel file could be in the wrong format, but I'm not sure??? The process is this: A user logs on to a webpage, runs a report that gets displayed in a gridview - then they export it to Excel. They make changes to the Excel document and then upload it and execute a dts to perform an update so to speak. Everything works fine except the DTS package. I was thinking that since we are exporting a gridview to an excel file, that it might not be a compatible file. Anyone ...

How to view the TOTAL values in the 3D graphic- from a Pivotal tabel
Hello I have a pivotal table, and I have created a 3D graphic to show th results. However I would like to have the TOTAL (for each supplier) in th graphic as well, and I do not know how to do it. I am sending the file where is easy to understand ... Anyone can help? Thanks a lot in advance Kind Regards Mart Attachment filename: question about the total.xls Download attachment: http://www.excelforum.com/attachment.php?postid=69739 -- Message posted from http://www.ExcelForum.com I don't know of any way to include the grand total in a PivotChart. You can create a norma...

convert matrices to table
Hi to everyone, is it possible to view a matrix in table mode? I have a matrix like this b c d e f ... b 1 0 3 4 5 c 2 5 8 6 5 d 2 3 6 8 e f .... I want to convert it in a 3 row table: ROW COL VALUE b b 1 b c 0 b d 3 b e 4 .... In one word, that's the opposite of what the pivot table does! :S thanks! Dave Hi Then use a Pivot Table to achieve it. You will have to insert an entry in cell A1, as the PT will complain there is no header val...

Can't Delete, Copy, or Move a mail Item (Exch 2003
I cannot delete, move or copy a mail item from a users mailbox. A message is displayed that says "The item could not be deleted. It was either moved or already deleted, or access was denied." So the message is stuck I've already tried disabling Cached Exchange Mode but that did not work. I've found one item in the Knowledge Base related to this issue (ID:3263296 Title: XADM: Cannot Delete or Move Messages in Messaging Client) but it relates to Exchange 2000 Only. We are running Exchange 2003 with Outlook 2003 as the mail client. Is there any thing I can do. Would an ...