MS Excel VBA Pivot table link cell fetch records

I have a table 'tbl_Final' in MS Access 2007 where the data is used
and cached in a pivot
table found in the 'Data' worksheet in MS Excel 2007.

In this MS Excel 2007 workbook, I have various worksheets reports
which links to the pivot table values
found 'Data' worksheet.

I linked formulae in one of the cells found the worksheet reports,
looks like this:

=GETPIVOTDATA("Amount",Data!$B$4,"Month",
2,"Year","2008","Scenario","Budget")

Lets say the cell value total is: 1000

So, In pivot: 1000
    In worksheet report: 1000


If I go to pivot table where this '1000' value is and double click on
this value, MS Excel builds
another worksheet with all the individual transactions that makes up
the '1000' value.

If I then go to the '1000' value found in the worksheet reports; this
link, when double clicked, will NOT
build another worksheet with all the individual transactions that
makes up the '1000' value.

Question: Is it possible using VBA to build some functionality in MS
Excell to allow the '1000' value found
in the worksheet reports, when double clicked, on that cell to build
another worksheet with all the individual
transactions that makes up the '1000' value?

I'm not sure how to write this MS Excel VBA logic if doable.

Any help appreicated

Many thanks in advance.
0
asxastro
12/8/2009 2:26:45 PM
excel.programming 6508 articles. 2 followers. Follow

1 Replies
1161 Views

Similar Articles

[PageSpeed] 26

Sorry to get your hopes up but my reply is not for the problem you had but rather a query about whether you solved this as I want to do the same thing that you asked about.



asxastro wrote:

MS Excel VBA Pivot table link cell fetch records
08-Dec-09

I have a table 'tbl_Final' in MS Access 2007 where the data is used
and cached in a pivot
table found in the 'Data' worksheet in MS Excel 2007.

In this MS Excel 2007 workbook, I have various worksheets reports
which links to the pivot table values
found 'Data' worksheet.

I linked formulae in one of the cells found the worksheet reports,
looks like this:

=GETPIVOTDATA("Amount",Data!$B$4,"Month",
2,"Year","2008","Scenario","Budget")

Lets say the cell value total is: 1000

So, In pivot: 1000
In worksheet report: 1000


If I go to pivot table where this '1000' value is and double click on
this value, MS Excel builds
another worksheet with all the individual transactions that makes up
the '1000' value.

If I then go to the '1000' value found in the worksheet reports; this
link, when double clicked, will NOT
build another worksheet with all the individual transactions that
makes up the '1000' value.

Question: Is it possible using VBA to build some functionality in MS
Excell to allow the '1000' value found
in the worksheet reports, when double clicked, on that cell to build
another worksheet with all the individual
transactions that makes up the '1000' value?

I am not sure how to write this MS Excel VBA logic if doable.

Any help appreicated

Many thanks in advance.

Previous Posts In This Thread:

On Tuesday, December 08, 2009 9:46 AM
asxastro wrote:

MS Excel VBA Pivot table link cell fetch records
I have a table 'tbl_Final' in MS Access 2007 where the data is used
and cached in a pivot
table found in the 'Data' worksheet in MS Excel 2007.

In this MS Excel 2007 workbook, I have various worksheets reports
which links to the pivot table values
found 'Data' worksheet.

I linked formulae in one of the cells found the worksheet reports,
looks like this:

=GETPIVOTDATA("Amount",Data!$B$4,"Month",
2,"Year","2008","Scenario","Budget")

Lets say the cell value total is: 1000

So, In pivot: 1000
In worksheet report: 1000


If I go to pivot table where this '1000' value is and double click on
this value, MS Excel builds
another worksheet with all the individual transactions that makes up
the '1000' value.

If I then go to the '1000' value found in the worksheet reports; this
link, when double clicked, will NOT
build another worksheet with all the individual transactions that
makes up the '1000' value.

Question: Is it possible using VBA to build some functionality in MS
Excell to allow the '1000' value found
in the worksheet reports, when double clicked, on that cell to build
another worksheet with all the individual
transactions that makes up the '1000' value?

I am not sure how to write this MS Excel VBA logic if doable.

Any help appreicated

Many thanks in advance.


Submitted via EggHeadCafe - Software Developer Portal of Choice 
How to Annotate Images from a database in a web page
http://www.eggheadcafe.com/tutorials/aspnet/0c4c3072-6730-41db-a759-082c24acd999/how-to-annotate-images-fr.aspx
0
peter
3/10/2010 5:47:06 AM
Reply:

Similar Artilces:

How can I write vertically in some columns on an Excel spreadshee.
I am a teacher and take attendance on Excel spreadsheets. I want to vertically write 'Christmas Holiday" or other wording in some columns. I would imagine there is a way, perhaps using PowerPointm but I do not know it. Also I have not been able to find it in 'help'. Any help out there? Select the cell that contains the text Choose Format>Cells Select the Alignment tab In the Orientation section, click on the Vertical text, or drag the red diamond to rotate the text Click OK Teacher using spreadsheets wrote: > I am a teacher and take attendance on Excel spread...

excel #41
Can you put a list into alphabetical order using excel Select the list - Data=>sort -- Regards, Tom Ogilvy sean <anonymous@discussions.microsoft.com> wrote in message news:D5907F16-F453-4458-94E5-FBA82BFDDB46@microsoft.com... > Can you put a list into alphabetical order using excel Yes you can. You need to highlight the entire worksheet and then index by the selected field that you want in alphabetical order ----- sean wrote: ---- Can you put a list into alphabetical order using excel ...

MS Outlook 2007
I am running MS Outlook 2007 on a Vista home lap top. When trying to delete files (or folders) in the Deleted Items folder, I get a message that "the Folder is full" and the items remain. I "right click" on Deleted Items, select "Empty 'Deleted Items' Folder", and I get the usueal warning msg that states "Are you sure you want to permanently delete all the items and subfolders from the 'Deleted Items' folder?" I click on "Yes". I then get a second warning msg that says "The folder is full." The ite...

Use of MS Dynamics CRM in Utility Billing
Hello Friendz, I was wondering if we can easily use Microsoft Dynamics CRM 3.0 for 'Utility Billing System and Print Presentment'. This also involves use of handheld devices for reading meters and bill presentment. In case you know about a better solution for this requirement, please share that with me. -- Regards, Dave Banerjee Cell: 0060 17331 4676 | email: IfThenElseEndIf@msn.com | IfThenElseEndIf@GMail.com That depends on what you mean by "easily" ;-) CRM out of the box will provide a great, flexible platform to quickly develop a robust business solution. Fro...

Dynamics 10.0 and VBA Form
I have created a VBA Form that is opened by clicking a button added by modifier. The form is used to select from a list of Crystal Reports that can be run from the window. In GP 9.0 the VBA Form opened on top of the GP form, with both Dynamics Form and VBA form being visible In Dynamics 10, when the form opens, it minimizes the GP window that has the button that activates the form, but only if the main Dynamics 10 menu window is not minimized. If I minimize the main Dynamics 10 Menu window, but leave the GP window visible, then click the button, the VBA Form opens on top of the GP form...

MS Office 97 to MS Office 2007
I have been running Office 97 pro on Windows XP because I do not want to loose the shortcut bar that came with 97. Thing is that I have been thinking about installing the 2007 version of Office that I have had but never installed yet for above reasons. Question is can I install the 2007 version over the 97 version and have it update the programs but keep the shortcut bar?? I know there are probably other shortcut bars or whatever out there but as I asked I would rather keep the 97 one if possible. TIA Microsoft has several Knowledge Base articles on running multiple...

Military Time in Excel
I would like to create a timetable in Excel with the format in the 24 hour clock without the colon, which I believe is known as military time. I am leaving the colon out for a number of reasons, aesthetic and to speed up entry. I wish to enter the time, for example, 8:30 am as 0800 and have Excel interpret it as a time, as these will be graphed, so need to be interpreted as time, and not plainly a number. At the moment Excel simply shows "800" when I type 0800. I have tried formatting all the cells as Custom "hhmm" instead of "hh:mm", but when ...

Difference between MS EPM and MS Projects
What is the difference between MS EPM and MS projects Is it just project server and sharepoint added to MS projects together called MS EPM? Yes. EPM is Enterprise Project Management. Microsoft's solution is Project Professional plus Project Server which in turn uses SharePoint technology. -- Rod Gill Microsoft MVP for Project - http://www.project-systems.co.nz Author of the only book on Project VBA, see: http://www.projectvbabook.com "Project Mgt Noob" <Project Mgt Noob @discussions.microsoft.com> wrote in message news:A565E3DF-A7FC-4D2A-B2F1...

Allow Numeric Characters Only In A Cell
Hello! I am wanting to both limit a cell to numeric entry only and require 12-digits. Example of typical entry: 01-2005-12345. I have to ensure that the zero will show up at the start if that is what they need to enter and the dashes must fall in the string as shown. I do not want the user to have to key the dashes. I currently have been able to use "Format Cells" to ensure the zero shows up and the dashes automatically insert into the string by using custom format and 0#-####-##### , but I am not able to require 12-digits and limit to numeric entry only. Any help would be g...

MS Exange connection
It deals with Outlook 2003, running under Win XP home: A friend has two ISPs, one with standard POP and SMTP servers, no problem there. The second is a remote MS Exchange server. He connects to both via Internet using ADSL connection. Question: how to configure the Exchange server connection such that it remembers his ID and PW? I could not find anything under just about any thinkable tab in Outlook, email accounts, etc. Any help will be greatly appreciated. -- JanAdam ...

How do I do word count in MS Publisher?
Mike, Publisher doesn't have a word count; the work-around wound be manually count the words or you could try copy the text and paste into Word. -- Don - Publisher 2000� Vancouver, USA "Mike Finn" <Mike Finn@discussions.microsoft.com> wrote in message news:9EBF3083-D7EC-46E9-ADA2-E4616499399E@microsoft.com... > Why not just do a "Edit Story in Microsoft Word" instead of copy and paste? -- JoAnn Paules MVP Microsoft [Publisher] Tech Editor for "Microsoft Publisher 2007 For Dummies" "Don Schmidt" <Don Engineer@PNB.Retired_...

Combine Text from Multiple Cells
How do I combine text from mutliple cells into one cell? Hi Carter one option - well two really: if the information is in cells A1, B1 & C1 and you want it combined into D1 then in D1 type =A1 & " " & B1 & " " & C1 this will give you the contents of each with a space between OR =CONCATENATE(A1," ",B1," ",C1) which will give you exactly the same thing Hope this helps Cheers JulieD "Carter" <anonymous@discussions.microsoft.com> wrote in message news:5fc401c48ac0$9cc86830$a301280a@phx.gbl... > How do I combine tex...

How do i access microsoft excel on windows xp? #2
I recently purchased a pc that came with windows xp home. I thought the microsoft excel programme would come with it, but i can not find it anywhere. Does this mean i have to purchase it seperately? Yes; or as part of Microsoft Office. "ayanda" wrote: > I recently purchased a pc that came with windows xp home. I thought the > microsoft excel programme would come with it, but i can not find it anywhere. > Does this mean i have to purchase it seperately? ...

MS CRM Certification question
Hi all, I have a question regarding the CRM certification? What, if any, certifications are there available with MS CRM? Is there anything like CRM Professional? Have I earned any certification if I passed the Applications Professional exam? T.I.A -- Kuba Skalbania [MCAD] ...

Outlook diabling links in my incomming messages
All of the sudden, Outlook is disabling links in all incoming E-Mail messages. I do get a notice at the top of the screen that "links are disabled, click here to enable (not recommended)", I can click there and it re-enables them. But I want links fully enabled by default at all times, like it was until last week (when I think I installed an Office patch or service pack from Microsoft Update). Does anyone know either where the settings are to fully enable links, or the manual registry patch that I would need to make? [I've already installed one to allow .exe and other ...

excel add-ons
Awhile back, there was an add-on for excel that had a lot more tools. It showed on the toolbar. Somehow I lost it and I would like to get it back. It had a lot of tools like for changing case and fill ins. Any help wouyld be great. Maybe ASAP Utilities. Try a 'net search for this add-in. Gord Dibben MS Excel MVP On Sun, 22 Mar 2009 20:22:43 -0400, "kmkx70a" <kmkx70a@prodigy.net> wrote: >Awhile back, there was an add-on for excel that had a lot more tools. It >showed on the toolbar. Somehow I lost it and I would like to get it back. It >had a lot of t...

How do I compare two lists of names in excel?
I am comparing 2 lists of attendances to conferences, so there are large numbers to go through, and 1 list will have more attendees than the other. Is there any formula I can use in excel to highlight names that appear only in the larger of the 2 lists? Chip Pearson has information on finding and tagging duplicate entries in a list. http://www.cpearson.com/excel/duplicat.htm Jack the Cate wrote: > I am comparing 2 lists of attendances to conferences, so there are large > numbers to go through, and 1 list will have more attendees than the other. > Is there any formul...

Please help with Excel VB question
Hi, I'm trying to set a Name range in Excel through VB, by setting the name property when a specific cell in a worksheet is changed. The code is as follows: fred1 = CStr("=PL!$A$4:$E$" Worksheets("Control").Range("maxnumpass").Value) ActiveWorkbook.Names.Add Name:="numpasslist", RefersTo:=fred1 My problem is that the change is only active for the worksheet where I change the cell. Is it possible to ensure that this change takes effect across all worksheets in the workbook? Any and all help appreciated either in a reply to this post or on jgal...

MS Money 2005 Basics
I am new to MS Money after many years with Quicken. Money help offline and online, seems woefully short of detailed explanations o user scenarios. . Searching help for "Epay" yields nothing. Ca someone either direct me to a better help area or answer this In Bills what is the difference between Epay, APay, and Direct debit I see Apay is how i use the bill pay service of my bank. Is direc debit just my manual entry? What then is Epay? What might "Other payment type then be used for? Thanks The difference between them is this Epay is for electronic bill pay. You must have Bi...

auto file path update when excel sheet moved to another directory.
Hello, I've been working on a huge excel file for a while and I'm trying to make it easier to use. My problem is that when it is moved from the template folder to a working folder the formulas referencing a file in the template folder have their path changed in the formula and have to have the links updated. Since the file is protected it's impossible for the users to chance the source. It's wierd because it will still pull information, but if information is changed in the external data file the updated doesn't get ported over. I need some like the $ command to ...

Multiple formats in a cell 11-18-09
Can anyone offer a solution to the issue of allowing a cell to display a value in two formats? I have a spreadsheet and in a cell I have a lookup which checks a table depending on what is selected from a list, the table has two different values Number and Percentage, these values are pulled into a summary screen to complete a sentance. I can only get the value to display as either a number or a percentage. Any suggestions please? Thanks, Gridlock. Hi, You could try this. This assumed any value less that 1 as a percentage. Also, the output will be a text value and not a nu...

Sent items downloading problem in MS office outlook 2007
Hi all, In my client pc's i have configured the Microsoft office outlook 2007 after the configuration inbox mails are visible in inbox but sent items and other folders are not getting downloaded, my mail server is squirrel mail ,so please let me know the reason . POP3 or IMAP? "xpmate" <sreekanth.chn@gmail.com> wrote in message news:41cb29fe-c615-44b3-aa86-ea3eb607e2a5@g22g2000prf.googlegroups.com... > Hi all, > > In my client pc's i have configured the Microsoft office outlook > 2007 after the configuration inbox mails are visible in in...

MS Access
Why doesn't MS support MS Access for Macintosh? Since I work in an educational environment that's mostly PC with MS Office begin the main suite, it would only be logical that since the PC version of MS Office contains MS Access for database entry, the Mac version of MS Office would also have MS Access. In article <BE09D66D.5FB8%redclawx@hotmail.com>, Red Claw <redclawx@hotmail.com> wrote: > Why doesn't MS support MS Access for Macintosh? Since I work in an > educational environment that's mostly PC with MS Office begin the main > suite, it would only ...

My Excel file won't close on my Mac
Version: 2008 Hi - <br><br>I have an excel file open on my mac and i can't close it - I can't save it - I can do anything with it - all the functions on the tool bar have adopted ghost functionality. I've tried to open the file again and this message appears: <br><br>'A dialog or alert is currently open. close the dialog or alert and open the file again.' <br><br>I can't close the file though - <br><br>Any thoughts? Thanks for your help. <br><br>Cian On 3/18/10 11:21 AM, Cian_Flood@officeformac.com ...

Starting MS Outlook on startup
How can I start Outlook on startup (XP) and keep it running in the back? >-----Original Message----- >How can I start Outlook on startup (XP) and keep it running in the back? > > >. > All you need to do is move the Outlook program icon to the startup group in XP. >-----Original Message----- >How can I start Outlook on startup (XP) and keep it running in the back? > > >. >Double click My Computer, go into the C:\Documents and Settings\User\Start Menu\Programs\Startup Place an Outlook shortcut into this folder and it will automatically startup when...