Pivot Table question #4

Hi

I have spreadsheets that take data via a pivot table from an OLAP cube that 
is held on a terminal server.

The spreadsheets hold figures entered by me and figures obtain from the 
pivot table.

I want to enable the automatic update on the pivot table but the pivot table 
is sorted by a project number obtained from the OLAP cube.  My problem is if 
a new project is added to the OLAP cube then it is automatically selected 
from the drop down list.  I want to be able to turn this off so that it only 
selects the project I have previously chosed.  For example if I have selected 
the filter number of 518 and a new project is added to the OLAP cube of 
ABC123 when a refresh of the data is enabled it will select both 518 and 
ABC123 and I want it to only select 518 on the refresh.

How do I do it please.

Thanks
0
2/10/2009 2:25:01 PM
excel.charting 18370 articles. 0 followers. Follow

1 Replies
590 Views

Similar Articles

[PageSpeed] 2

Hi,

I don't think there is any built-in way to do this.  You could write VBA 
code the responds to the Refresh event.  But you are going to need to save 
the prior setting before triggering refresh and then reapply them after the 
refresh.

You will want to look at this event:

Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)

End Sub

-- 
If this helps, please click the Yes button

Cheers,
Shane Devenshire


"Beverly Darvill" wrote:

> Hi
> 
> I have spreadsheets that take data via a pivot table from an OLAP cube that 
> is held on a terminal server.
> 
> The spreadsheets hold figures entered by me and figures obtain from the 
> pivot table.
> 
> I want to enable the automatic update on the pivot table but the pivot table 
> is sorted by a project number obtained from the OLAP cube.  My problem is if 
> a new project is added to the OLAP cube then it is automatically selected 
> from the drop down list.  I want to be able to turn this off so that it only 
> selects the project I have previously chosed.  For example if I have selected 
> the filter number of 518 and a new project is added to the OLAP cube of 
> ABC123 when a refresh of the data is enabled it will select both 518 and 
> ABC123 and I want it to only select 518 on the refresh.
> 
> How do I do it please.
> 
> Thanks
0
2/11/2009 12:29:01 AM
Reply:

Similar Artilces:

Relaying Question.....Stumped
Hello, our relay is closed, or seems to be. when we send to certain domains we get the following error: 550 smtp, relaying blocked, read new mail, add "exchange server's public ip address" to forwarding or enable smtp authentication in your can anyone give me some help on this one? just dont want to turn something on im not suppose to. thanks for all help.. Bob C. Hi Bob, "Bob Chyka" <Bob Chyka@discussions.microsoft.com> schrieb im Newsbeitrag news:F64F6792-08D5-418E-B3D3-7AE2F86841FC@microsoft.com... > Hello, > > our relay is closed, or seem...

Pivot Table 12-03-09
I have a Pivot Table that has YEAR AND DATA as rows, and MONTH as a column, like this: MONTH YEAR DATA Each of the items YEAR, DATA and MONTH have drop down boxes to select various criteria. For example, DATA allows me to select "Average of High", "Average of Low" and "Show All" When I select "Show All" for the Data item, the Pivot table is fine. But if I select Average of High for the Data item, DATA changes to "Average of High" and the drop down box is no longer available. Three questions: 1. ...

Extrapolation and Interpolation questions
Hi there, I am currently facing few problems with my report. The first thing is, how do I extrapolate a graph? I have a series of data from experiment which shows roughly a "mountain" like graph where there is ascending zone, peak zone, then descending zone. However, I didn't have enough value in the descending zone to plot a longer graph. How do I extrapolate? Second question, is how to find X value. Imagine the "mountain" shaped graph. At one Y axis, there should be 2 X axis value since there an ascending and descending zone in the graph. Is there any function to h...

PropertySheet Question
Hi, How do I add or remove a tab in PropertyPage after it is shown. I have 2 standard pages and some alternative pages in a property sheet - that is whether I show page 3, 4 should depend on what the user selects in page 1. How do I implement this? Thanks I noodled around and found that I get the Tab Control from the PropertySheet and remove my items, bu Add the page (items) from the PropertySheet class. I don't know if this is the correct way, but seems to work so far. "rajas" <rajas@cox.net> wrote in message news:McEec.583$Yf6.542@fed1read07... > Hi, > >...

Extender views to be linked to SOP_HDR_WORK table for reports
Dear All, I have created an extender window for sales transaction entry window and saved the data in that extender and later created a view of that extender window and now i want to link this veiw to SOP_HDR_WORK table to utilized the extender fields in the sales order reports but in the link table area I could not find this view. So kindly guide me about how to link this enxtender winow to gp tables. Your immediate reply will be highly appreciated. Thanx in advance. -- Developer Please ignore the below question because just now I found the solution which is RW functi...

How do I stop an pivot report from losing conditional formatting
I have a pivot table that needs updated weekly and everytime I refresh the report it loses its formatting and I start over. This report conditional formatting needed reflects changes in color when collections drop for 3 consecutive months in a row. example Oct Nov Dec Jan 451 390 280 180 the report needs to change color first month drop yellow, second orange and third and additional months that have dropped turn red. Any assistance on how to handle conditional formatting and prevent it from losing when refreshed will be appreciated. ...

Inserting Picture in Table for Form
I am creating a form that will make use of various graphic images (Line Drawings) that I hope to create using some program such as Daw. I have added an OLE field to the table, and included it as the data for a control that takes up about half the screen on a form. When I open the form all I get are two litttle boxes in the upper right hand corner of the control, and when I click on the control, it says the text is too large to be edited. This is the first time I have done anything like this. I am using Access 2000, and am fairly familiar with VBA if any coding is needed. Do I have...

Updating Dynamic Pivot Tables
We created a dynamic pivot table and that works. However, when we modify the underlying query in CRM to include or exclude certain records (i.e. different date range or status reasons), the pivot table data in Excel does not reflect the changed query. Is this not supported, or are we missing a trick here? Grateful for any insight.... Yes, the pivot table can display new data from CRM. You can select Refresh data or set the refresh interval to the appropriate interval you need. I'm using Excel 2007 and it defaults to 60 minute intervals. You can set it to the interval you like. &...

Mailboxes #4
Hi all, i just installed EXCH2003 (fresh install) to have a look!! After i create a new user with a mailbox (with AD users and computers) , i go to system manager, then in first storage group/mailbox store/mailboxes to see the mailbox BUT i don't see any the mailbox that i created, i see only system attendant mailbox and system mailbox !! Where can i see the list of mailbox that i created?? isn't suppose to be there?? thanks !! The User mailbox that you create must be logon or send message to the mailbox,after you can see mailbox in ESM "Rick" <Rick@discussions....

Excel atuo filling cells- question
I'm working with a spreadsheet (MS Office/Excel 2003) that someone else set up, and certain cells, which I add to every day, will automatically fill in with a background color when data is entered, which is pretty nifty. But certail cells don't, even though they should (should meaning that color-scheme wise it would be nice if they did). But I can't figure out why they won't or how it's programmed to know when/and what color to do that with. The strange thing is, I know these cells (they're in a particular column) used to do that, and some of the other cells *sometimes*...

Scrollbar Question
Does anybody know how I can set the size of the thumb of the scrollbar? I tried using SetScrollInfo with SIF_PAGE/nPage but it doesn't work. Thanks, Vincent This article might help: http://www.codeproject.com/miscctrl/scrollbar.asp?print=true Tom "Vincent YU" <anonymous@discussions.microsoft.com> wrote in message news:153b01c3a88f$e584c820$a601280a@phx.gbl... > Does anybody know how I can set the size of the thumb of > the scrollbar? I tried using SetScrollInfo with > SIF_PAGE/nPage but it doesn't work. > > Thanks, > Vincent ...

how to implement a separate address table
In my database I have a People table and an Address table. The relationship is many-to-many - multiple people (in the same family) can have the same address, and one person can have multiple addresses (home, work, etc.), so I have a interim table linking PeopleID and AddressID (with a boolean CurrentAddress flag). I also have a table linking People to People, so family relationships can be managed. The database design is easy enough, but the form I created to manage all this became a little clumsy to use. It works OK, but I wondered if anyone has any nifty ideas I didn't think ...

Help! need to fit 4 - 3.75"x5.25" cards (all different) on one page
Hi, I am new to ms publisher 2003, and I am trying to create a document that does the following: - about 50 unique thankyou cards - fit 4 or so on one page Thanks! When you say 'unique' do you mean you are Merging data and/or pictures to the cards 4 to a page? A little more information please? -- The US should free all those illegally held prisoners they are torturing, abusing and denying human rights being held at Guantanamo Bay. Hi, Each card will be typed manually, and I would like to fit for of these cards on one page/sheet of paper (to save paper). I will then cut ...

questions on advanced filter
I'm trying to use an advanced filter to eliminate don't care rows from a large data set and the filter is not behaving as I expect. My dataset has 22203 rows plus a header row, I', reduced the data to just the one column that I need to filter on. My criteria set set is 155 rows plus an identical header row, again only one column. In all of my tests I have selected all 22204 row for the source data, and I'm filtering in place (ultimately I will want to filter to a different destination.) Also all of my filter criteria are of the form: <>*term1* <>*...

Pivot table overwrites neighbouring cells
Hello, I have multiple pivot tables on the same sheet to allow for easy comparison of data. The source data is a web query that is based on weekly data. As the week progresses, the size of the pivot tables will increase and overwrite data below it. I am looking for a workaround or if there is a way to set the pivot table to insert a new row below it if the table size will increase. Thanks in advance! Anissa ...

Rules Wizard: Auto Response
I want to use the rules wizard to send a response automatically to all messages sent to me with the same subject. The problem is, Outlook 2004 will only do this for the first message each session, which is troublesome. (I do not have access to an Exchange server.) How can I configure Outlook 2004 to do this each time such a message is received? I am using a template to respond, but I would like to have the template automatically include the sender's message text, to include mail header (or whatever will show that the attachment they sent was received by me). How can I do this? ...

Budget Changes to Effect Prior Periods Question
Money 2004 -- If you add a new monthly budget item through a recurring bill in November of 2003, how can you get it to effect your annual budget report for 2003 such that the budget amount shows for all prior months of 2003. For example, you have been paying rent by writing a check each month (Jan-Oct) and then you set up a recurring bill in November. On the annual budget report there will be nothing budgeted for Jan-Oct, only expenses occurred and therefore over budget. -- Kent Riesen Eureka, CA ...

another countif question
Can I select cells to 'countif' instead of selecting a range of cells? As in every other cell, or every 3 cells? In that situation, you'd be better off with SUMPRODUCT. -- David Biddulph DebC wrote: > Can I select cells to 'countif' instead of selecting a range of > cells? As in every other cell, or every 3 cells? Be more specific. Need more info. -- Biff Microsoft Excel MVP "DebC" <DebC@discussions.microsoft.com> wrote in message news:61FF7855-D2DE-40C3-983F-DA33A1BBC138@microsoft.com... > Can I select cells to 'c...

Send Receive Error #4
5:41 PM 10/8/2007 Microsoft Outlook 2003 stopped sending email. After I choose to send messages from the menu, the program attempts to send a message for � 30 seconds; then I get an error message:"Send Receive Error -- Click Here for Details". Then the error message disappears after about � second -- I cannot get details. How can I fix Outlook? Thank you, Barry Karas P.S. I have Windows XP with SP2. ...

Action Button Question
I have a button that is unbound. I also have a text box. I want to program the button that when clicked it will place the current date and time at the end of all text (if applicable) in the text box. Let me thank you a head of time Put code like this on the click event of the button. Me.txt1 = Me.txt1 & " " & Now() note: replace txt1 with your own object name. Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia "Timothy Millar" <Tim Millar@discussions.microsoft.com> wrote in message news:F1E6502D-F609-45FC-9F81-03C1B273...

WSE 3.0 X.509 General Question
I'm migrating my app to WSE 3.0. In the past I used WSE 1.0 and 2.0 to encrypt my requests and responses using a private shared key, and not using X.509 due to perceived difficulties therein. With each release of WSE, however, I find myself having to re-implement my encryption scheme to make it work, since it's not using the recommended X.509 certs. Is it possible to use X.509 in a "private" environment, i.e. closed network (no internet), between one server and one or more clients? I'm assuming this would require the IIS Web Server to act as a Certificate Author...

Setting up tables properly
Hi I have started creating a database and realize that I haven't set it up properly. Currently I have about 10 tables in my database. In each table, I have the Student and Fields included. Some of these tables are currently "Students", "Contact information" (for emergency contacts), "Needs assessment", "General Information" (which includes address), "Courses taken". The reason for this was that I thought I had to create individual tables to reflect a report. By the way, I have already created some forms for this data...

excel graphs data labels in Pivot tables
I have a pivot table, and i have certain events, listed under the comments column that i would like those comments to appear on the graph by the date each major event happened in? ...

Question on SQLConfigDataSource
I'm trying to setup SQLConfigDataSource for a program i'm currently working on. The Access database I'm using is on a LAN server, not the local computer. When calling SQLConfigDataSource, the result is C:\server\DEFAULTDIR, not \\server\DEFAULTDIR. How do i configure this for the network server? Thanks for your help! Paul ...

another formula question
I know how to do a basic formula that adds 2 cells but when I drag that formula down to copy it to other cells where there is no total due to no data entered yet it shows $0.00. besides +A1+B1 is there anything else that I can add to the formula so that the cell is blank untill there is a total to display, in other words, get rid of the $0.00 in all the cells Try using an =if formula so instead of +A1+B1, use: =if(A1+B1<>0,A1+B1,"") in this formula, the if statement checks whether A1+B1 is not equal to zero. If it isn't, it puts in the value A1+B1, if it does ...