create pivot table formula without the GETPIVOTDATA function

Previous to Excel 2003 I could use a pivot table result in a formula and copy 
that formula as a relative reference.  In Excel 2003 a GETPIVOTDATA fromula 
is created and uses an absolute reference.  Is there a way in Excel 2003 to 
either make it arelative reference (without editing the formula) or revert to 
the method used in all prior releases?
0
ebergkes (2)
11/10/2005 10:30:04 PM
excel.misc 78881 articles. 5 followers. Follow

3 Replies
538 Views

Similar Articles

[PageSpeed] 2

There are instructions here for adding the Generate GetPivotData button
to a toolbar, and toggling the feature on and off:

       http://www.contextures.com/xlPivot06.html

ebergkes wrote:
> Previous to Excel 2003 I could use a pivot table result in a formula and copy 
> that formula as a relative reference.  In Excel 2003 a GETPIVOTDATA fromula 
> is created and uses an absolute reference.  Is there a way in Excel 2003 to 
> either make it arelative reference (without editing the formula) or revert to 
> the method used in all prior releases?


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

0
dsd1 (5911)
11/10/2005 10:43:34 PM
After posting I saw reference to your site and found the answer there - great 
site.  Thank you!

"Debra Dalgleish" wrote:

> There are instructions here for adding the Generate GetPivotData button
> to a toolbar, and toggling the feature on and off:
> 
>        http://www.contextures.com/xlPivot06.html
> 
> ebergkes wrote:
> > Previous to Excel 2003 I could use a pivot table result in a formula and copy 
> > that formula as a relative reference.  In Excel 2003 a GETPIVOTDATA fromula 
> > is created and uses an absolute reference.  Is there a way in Excel 2003 to 
> > either make it arelative reference (without editing the formula) or revert to 
> > the method used in all prior releases?
> 
> 
> -- 
> Debra Dalgleish
> Excel FAQ, Tips & Book List
> http://www.contextures.com/tiptech.html
> 
> 
0
ebergkes (2)
11/10/2005 11:00:03 PM
You're welcome!

ebergkes wrote:
> After posting I saw reference to your site and found the answer there - great 
> site.  Thank you!
> 
> "Debra Dalgleish" wrote:
> 
> 
>>There are instructions here for adding the Generate GetPivotData button
>>to a toolbar, and toggling the feature on and off:
>>
>>       http://www.contextures.com/xlPivot06.html
>>
>>ebergkes wrote:
>>
>>>Previous to Excel 2003 I could use a pivot table result in a formula and copy 
>>>that formula as a relative reference.  In Excel 2003 a GETPIVOTDATA fromula 
>>>is created and uses an absolute reference.  Is there a way in Excel 2003 to 
>>>either make it arelative reference (without editing the formula) or revert to 
>>>the method used in all prior releases?
>>
>>
>>-- 
>>Debra Dalgleish
>>Excel FAQ, Tips & Book List
>>http://www.contextures.com/tiptech.html
>>
>>
> 


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

0
dsd1 (5911)
11/11/2005 12:25:05 AM
Reply:

Similar Artilces:

Non-VBA formula to find 2nd Sunday of a given month
Can anyone help me write a formula to find the date of the second sunda in a given month? Thanks in Advance, Dav ----------------------------------------------- ~~ Message posted from http://www.ExcelTip.com ~~View and post usenet messages directly from http://www.ExcelForum.com David, Assuming you have a date in A1, this gives the 2nd Sunday of that date =DATE(YEAR(A1),MONTH(A1),1)+MOD(8-WEEKDAY(DATE(YEAR(A1),MONTH(A1),1)),7)+7 -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "DavidObeid&q...

Excel Lookup formulas
Hi, I have two questions: 1. I notice (occassionally) when I am using Vlookup/Hlookup to import data to another worksheet that the rules to not immediatley reflect the correct data. However, if I click on each data cell, and validate the formula (by clicking on the 'tick') it immediatley returns the correct data. Is there anyway of doing a bulk refresh that will somehow re-activate the rules that appear to have become dormant? 2. Is there a formula that will find the largest numeric data entry in a row, column, array? Thankyou, Don- Re part 2 of your question =max(place range...

Tough Formula Question
Scenario: Sheet with db output regarding product sales. Second sheet w/ trend line formulas to predict how many to order for the next month. Works fine. Here's the poblem. Lets say it predicts I sould purchase 4 bottles of coke. Coke is only sold in incriments of 6 bottles. How to I make it round up by incriments of order number. The order incriments are part of the db output so that should help. I'm pretty sure I'll always want to round up. This cannot be a vb problem. It needs to be a formula. Thanks for any help and good luck. Hi Try =CEILING(number,order_size) =CEILING(8...

How do I create a database from an Excel form?
Hello Everybody - I need to see if Excel can be used to create a mailing list database from an Excel form. If so...how do I go about doing this? The list will need to include the name of the company, contact person, and address. Thanks in advance for all of your help! It is greatly appreciated! Here are a couple of links: http://www.mvps.org/dmcritchie/excel/mailmerg.htm http://www.mvps.org/word/FAQs/MailMerge The first is from David McRitchie and the second is by Beth Melton and Dave Rado. FIRESIGN98 wrote: > > Hello Everybody - > > I need to see if Excel can be used...

Formula help/suggestions?
I'm looking for help some help with a formula. How would I go about a formula for automatically adding 8 hours to a number each calendar month? I would like to open it and have it done for me if that's possible rather than having to remember to add it each month or forgetting if I did or not. For instance, i start out with the # 248 in a cell that I would like to automatically add 8 to each calendar month. "Gadgetman" <stoojz3@yahoo.com> wrote in message news:stoojz3-96EE83.15151022012011@usenet.ptd.net... > I'm looking for help some help with a formula....

Create calendar item/mtg request when people register for webinar?
Hi! I need assistance with figuring out how to generate an Outlook calendar item whenever people register for a webinar or other activity through our website. Is a plug-in available? Ideally, whenever someone signs up for an activity through our website, it would then forward a meeting item to their Outlook calendar. I've seen this done in so many places. "jasovi" <jasovi@discussions.microsoft.com> wrote in message news:EDED7311-4B37-4D4A-A0F5-B77C982D2014@microsoft.com... > Hi! I need assistance with figuring out how to generate an Outlook calendar &...

Creating a view with concatenated columns
I have a table with about 20 columns and I am trying to create a view of it that includes about 10 columns. The problem is that in the process, I am trying to concatenate some fields into one field and I am unable to get any thing working. Below is one example of my attempts to tackle this. Any ideas will be highly appreciated. // The following should be considered as pseudo-SQL CREATE function [dbo].[fn_myview] (@CardNo varchar(11)) returns Table as BEGIN declare @FullName as nvarchar(128) (SELECT FirstName, FatherName, GFatherName, FamilyName, Birthdate, Birthplace...

due date function
I try to make function for due payment. When I made copy file and then drag down the formula and it's succed. But for the cell that I not fill yet has due date also. How to make that cell became 0 (zero) if not fill the cell yet. example : A1 A2 19/8/05 =A1+30 - 30/01/00 - 30/01/00 Thanks Lando =IF(C5=""; "" ;C5+30) Explanation detail if ([logical test] c5 equals ""; then [result if true] ""; else [result if false] c5+30) Explanation brief if c5 eq...

I need help with a formula
Hi everyone I'm making a stock list sheet and I like to put on it the value - and + .. like when I take boxes out it will give me the total and when I add in it will change also. This is for a weekly count. I'm very new to excel so please H-E-L-P-. I need the instruction like u give it to a child. :) :) Thanks a lot -- pampam ------------------------------------------------------------------------ pampam's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=26224 View this thread: http://www.excelforum.com/showthread.php?threadid=395241 We need ...

formula help 02-10-10
Hi- Issue: 211 310 410 569 Would like to accomplish: 2211 2310 2410 2569 How do I add number 2 infront of the above ISSUE numbers as I have thousands of line to do. Soth If your number is in column A then in column B type =2 & A1 and copy it down. -- -John http://www.jmbundy.blogspot.com/ Please rate when your question is answered to help us and others know what is helpful. "Soth" wrote: > Hi- > > Issue: > 211 > 310 > 410 > 569 > > Would like to accomplish: > 2211 > 2310 > 2410 > 2...

empty pivot table fields
How can I force a pivot table to, for example, say that there were no accidents in week 2. At present it says 3 in week 1 then jumps to 2 in week 3. I want week 2 =0 for charting purposes. -thanks Right-click on the Weeks field button. Select Field Settings Add a check mark to 'Show items with no data' Click OK bobf wrote: > How can I force a pivot table to, for example, say that there were no > accidents in week 2. At present it says 3 in week 1 then jumps to 2 in week > 3. I want week 2 =0 for charting purposes. -thanks -- Debra Dalgleish Excel FAQ, Tips & Book...

type a word on the line without desturbing the line
I am using Outlook Publisher and I need to know how do I type on a line without desturbing the line. Not sure I understand. Do you want to overwrite a line of text? If you want to add a word without the line moving, use the character spacing option in the format menu. -- Mary Sauer MSFT MVP http://office.microsoft.com/ http://msauer.mvps.org/ news://msnews.microsoft.com "Bermuda Mama" <Bermuda Mama@discussions.microsoft.com> wrote in message news:615FB725-748E-4403-B41F-AB9CAFC19971@microsoft.com... >I am using Outlook Publisher and I need to know how do I type on a li...

Can an email be read without internet connection?
Am downloading an email to CD, but the computer I need the CD for has no internet connection at present. Can I read & print the email without access to internet? This is an HP notebook & the computer I need to read & print the CD in is an IBM Aptiva 247. Hula Girl wrote: > Am downloading an email to CD, but the computer I need the CD for has > no internet connection at present. Can I read & print the email > without access to internet? This is an HP notebook & the computer I > need to read & print the CD in is an IBM Aptiva 247. Not sure what you mean b...

Shift Schedule Formula
Hello. Need a formula that will return the shift name (A/E/C/F) in cell A2 based on the current system date and time. I am not sure how to make it happen. My shift schedule is below: A-Shift: 07:00 to 19:00 Sunday, Monday,Tuesday and every other Wednesday C-Shift: 07:00 to 19:00 Every other Wednesday, Thursday, Friday and Saturday E-Shift: 19:00 to 07:00 Every other Saturday, Sunday, Monday and Tuesday F-Shift: 19:00 to 07:00 Wednesday, Thursday, Friday and every other Saturday Is this possible? Thanks for your help. I created the following formula and put it in cell A2... it ...

Display Non Inventory description on Create Purchase Orders screen
Display the item Description for non-inventory items on the Create Purchase Orders screen. Currently, only inventory item descriptions are displayed. ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft Web-based Newsreader and then click "I Agree" in the message pane. http://www.microsoft.com/Businesssolutions/Community/NewsGroups/dgbrow...

Pivot Table Drill down
Is there a way for the drill down data to retain its Hyperlink. When I drill down on the Pivot Table I would like the original Hyperlink status from the source data to be retained. --- Message posted from http://www.ExcelForum.com/ Don't understand your problem here. Excel automatically retains the lin to external data so you can use menu 'Refresh' to update the table a any time - assuming the source is in the same place as before.. If you want to move a source file elsewhere, open the one with th pivot table first and use File/Copy, when the link will automaticall change (if ...

Convert Word Table -> Excel without merged cells?
Whenver I copy/paste tables from word to excel, I get merged cells in Excel whenever there are line breaks in the word table cells. How can I avoid this? TIA Steve Saved from a previous post: If your cells in your word table contain paragraph mark or linebreak characters, then excel will bring them over as separate cells. One way around it is to convert those paragraph marks & linebreaks to unique characters, then copy|paste and then convert them back to linefeeds. I like this technique (inside a copy of the word file): Select your table. Edit|replace|Special (show More if required)...

How to determine content type of URL without downloading it?
Hello there How can I determine content type of file or URL located on web without downloading a sinle bit. I could do it in .Net by using WebRequest class. But how can I achieve same in MFC ? Every file located on web has content type stored in HTTP header. How can I retrive it without downloading. Roland wrote: > How can I determine content type of file or URL located on web without > downloading a sinle bit. I could do it in .Net by using WebRequest > class. But how can I achieve same in MFC ? > Every file located on web has content type stored in HTTP header. How > can I re...

Transform a Cell from Formula to Number
Is there a way to turn a cell from a formula to the number the formula outputs? Copy it to the clipboard, then do Edit>PasteSpceial, click Values -- HTH Bob Phillips "Sloth" <Sloth@discussions.microsoft.com> wrote in message news:76DA206E-CFC3-4E63-9789-69735538E1A7@microsoft.com... > Is there a way to turn a cell from a formula to the number the formula outputs? Copy the cell. Edit/Paste Special/Values. In article <76DA206E-CFC3-4E63-9789-69735538E1A7@microsoft.com>, "Sloth" <Sloth@discussions.microsoft.com> wrote: > Is there a way to...

Unable to make a pivot table in sheet2
Hi, Can someone pls check below code. I'm able to export a query from access to excel in file "Myfile.xls" sheet1 but unable to make a sheet2 with pivot table of sheet1 data. Option Compare Database Public Sub TransferReport() Dim varFileName As String Dim xlApp As Object Dim xlWb As Object Dim xlWs As Object Dim MyRange As String varFileName = "D:\MyFile.xls" 'EXPORT DATA DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "MONTH END REPORT", varFileName, False, "Sheet1" Set xlWb = ActiveWorkbook xlWb.Sheets.Ad...

Unable to create mailboxes
Two weeks ago we created and installed a new exchange 2003 server in mixed mode in a 2000 active directory enviroment. We are still using the existing exchange 2000 server as a relay server. Since the upgrade when I create a new account in ADUC I see the exchange email tab populates correctly but nothing shows up under Exchange Advance tab. I also dont see the user name in the GAL. I'm seeing 8270 and 9176 in the event viewier. Does exchange require that I install the system manager on all the DC? Thanks, -Michael ...

Creating MFC child dialogs and windows in DLL.
Hi, I need to write some kind of plug-in, which are implemented as separate dll's. I already wrote a few plugins for this program using MFC and it worked ok, but now I need to create some GUI inside a plugin. I have an interface like that class IModule { public: virtual void ShowConfDialog( HWND hwndParent ) = 0; virtual HWND CreatePanel( HWND hwndParent ) =0; // create child window, position and size will be // controlled by a parent window. } But when creating a child windows in MFC I nee...

Vlookup
Version: 2008 Operating System: Mac OS X 10.6 (Snow Leopard) Processor: Intel Hi, <br> I'm really frustrated, and am wondering if i'm an idiot, or if there's a bug... <br> I have two colums, with 5 rows in each column. A1:B5. The first column is 5 names. The second column is 5 scores. I'm attempting to vlookup on the names, and retrieve the scores. <br> tom&#09;12 <br> barry 17 <br> jason 23 <br> lou 43 <br> andy 9 <br><br>My formula is =VLOOKUP(&quot;tom&quot;,A1:B5,2) I'm expecting '12' ...

SQL script to create views
I think I saw a script somewhere on CustomerSource that would crete views with Table Display Names - would anybody know where to find it? Thanks, Mario Do a search for SDK and GenView (or Gen View, and it's part of the SDK). I believe that's what it's called. It's not so much a script as it is a utility. Clint "Mario" <anonymous@discussions.microsoft.com> wrote in message news:2a5ee01c465b4$e345d620$a301280a@phx.gbl... > I think I saw a script somewhere on CustomerSource that > would crete views with Table Display Names - would anybody > know wh...

Can formulas in cells be made to remain if the data is deleted?
Can you explain in a bit more detail what your question/problem is? -- Kind regards, Niek Otten "wendyp" <wendyp@discussions.microsoft.com> wrote in message news:B7A92B0A-E752-4D58-90FF-3DE5F292D35C@microsoft.com... > Hi Wendy, See Insert a Row using a Macro to maintain formulas http://www.mvps.org/dmcritchie/excel/insrtrow.htm To simply remove constants from a selection within a macro Selection. SpecialCells(xlConstants).ClearContents To remove constants manually from a selection Edit, GoTo (Ctrl+G), Constans [you can pick what kind ...