remove Absolute references when copying from pivot table

Hi,
I have a pivot table of data in a workbook called Pivot MT with units as row 
names and months as column names. I have a separate workbook called Monthly 
Totals in which I am referencing certain values inside the pivot table.  I 
want to use the fill handle in this Monthly Totals workbook and drag accross 
the columns to capture certain values in the pivot table.  However, there 
seems to be an absolute values (i.e. $A$4) which causes all values across to 
be the same.  I want to remove the absolute values but F4 doesn't seem to 
work.  Can someone help?

Thanks,
0
ChuckW (8)
5/7/2009 2:31:02 PM
excel 39879 articles. 2 followers. Follow

4 Replies
1129 Views

Similar Articles

[PageSpeed] 21

On May 8, 12:31=A0am, Chuck W <Chu...@discussions.microsoft.com> wrote:
> Hi,
> I have a pivot table of data in a workbook called Pivot MT with units as =
row
> names and months as column names. I have a separate workbook called Month=
ly
> Totals in which I am referencing certain values inside the pivot table. =
=A0I
> want to use the fill handle in this Monthly Totals workbook and drag accr=
oss
> the columns to capture certain values in the pivot table. =A0However, the=
re
> seems to be an absolute values (i.e. $A$4) which causes all values across=
 to
> be the same. =A0I want to remove the absolute values but F4 doesn't seem =
to
> work. =A0Can someone help?
>
> Thanks,

Chuck,

Can you send the complete formula you are trying to drag in the
Monthly Totals sheet

That may give us a better understanding of what the problem is.

Regards

David
0
dheaton (109)
5/7/2009 11:38:06 PM
David,
Here is the formula.  I am trying to remove the $ through a function key but 
am not sure how to do it.  There are several of these that I need to do so I 
want to avoid manually going and removing them.
Thank,  Chuck

=GETPIVOTDATA(" WristX2",$A$4,"Month",DATE(2008,9,1))+GETPIVOTDATA(" 
WristX2",$A$4,"Month",DATE(2008,10,1))+GETPIVOTDATA(" 
WristX2",$A$4,"Month",DATE(2008,11,1))+GETPIVOTDATA(" 
WristX2",$A$4,"Month",DATE(2008,12,1))+GETPIVOTDATA(" 
WristX2",$A$4,"Month",DATE(2009,1,1))+GETPIVOTDATA(" 
WristX2",$A$4,"Month",DATE(2009,2,1))



"David Heaton" wrote:

> On May 8, 12:31 am, Chuck W <Chu...@discussions.microsoft.com> wrote:
> > Hi,
> > I have a pivot table of data in a workbook called Pivot MT with units as row
> > names and months as column names. I have a separate workbook called Monthly
> > Totals in which I am referencing certain values inside the pivot table.  I
> > want to use the fill handle in this Monthly Totals workbook and drag accross
> > the columns to capture certain values in the pivot table.  However, there
> > seems to be an absolute values (i.e. $A$4) which causes all values across to
> > be the same.  I want to remove the absolute values but F4 doesn't seem to
> > work.  Can someone help?
> >
> > Thanks,
> 
> Chuck,
> 
> Can you send the complete formula you are trying to drag in the
> Monthly Totals sheet
> 
> That may give us a better understanding of what the problem is.
> 
> Regards
> 
> David
> 
0
ChuckW (8)
5/8/2009 7:52:01 PM
If you're careful with selecting the range AND you don't use the $ in any
strings, then you how about trying:

Select the range
Edit|goto (or hit ctrl-g or F5)
Special
Formulas

(Selecting the formulas may not be necessary if you don't have any $'s in any
other cell.)

Then 
Edit|replace (ctrl-h)
what: $
with: (leave blank)
replace all

An alternative is having a macro that cycles through all the cells and changes
the absolute references to relative, but if the edit|replace stuff works, it'll
be faster.

Chuck W wrote:
> 
> David,
> Here is the formula.  I am trying to remove the $ through a function key but
> am not sure how to do it.  There are several of these that I need to do so I
> want to avoid manually going and removing them.
> Thank,  Chuck
> 
> =GETPIVOTDATA(" WristX2",$A$4,"Month",DATE(2008,9,1))+GETPIVOTDATA("
> WristX2",$A$4,"Month",DATE(2008,10,1))+GETPIVOTDATA("
> WristX2",$A$4,"Month",DATE(2008,11,1))+GETPIVOTDATA("
> WristX2",$A$4,"Month",DATE(2008,12,1))+GETPIVOTDATA("
> WristX2",$A$4,"Month",DATE(2009,1,1))+GETPIVOTDATA("
> WristX2",$A$4,"Month",DATE(2009,2,1))
> 
> "David Heaton" wrote:
> 
> > On May 8, 12:31 am, Chuck W <Chu...@discussions.microsoft.com> wrote:
> > > Hi,
> > > I have a pivot table of data in a workbook called Pivot MT with units as row
> > > names and months as column names. I have a separate workbook called Monthly
> > > Totals in which I am referencing certain values inside the pivot table.  I
> > > want to use the fill handle in this Monthly Totals workbook and drag accross
> > > the columns to capture certain values in the pivot table.  However, there
> > > seems to be an absolute values (i.e. $A$4) which causes all values across to
> > > be the same.  I want to remove the absolute values but F4 doesn't seem to
> > > work.  Can someone help?
> > >
> > > Thanks,
> >
> > Chuck,
> >
> > Can you send the complete formula you are trying to drag in the
> > Monthly Totals sheet
> >
> > That may give us a better understanding of what the problem is.
> >
> > Regards
> >
> > David
> >

-- 

Dave Peterson
0
petersod (12005)
5/8/2009 9:24:42 PM
On May 9, 7:24=A0am, Dave Peterson <peter...@verizonXSPAM.net> wrote:
> If you're careful with selecting the range AND you don't use the $ in any
> strings, then you how about trying:
>
> Select the range
> Edit|goto (or hit ctrl-g or F5)
> Special
> Formulas
>
> (Selecting the formulas may not be necessary if you don't have any $'s in=
 any
> other cell.)
>
> Then
> Edit|replace (ctrl-h)
> what: $
> with: (leave blank)
> replace all
>
> An alternative is having a macro that cycles through all the cells and ch=
anges
> the absolute references to relative, but if the edit|replace stuff works,=
 it'll
> be faster.
>
>
>
>
>
> Chuck W wrote:
>
> > David,
> > Here is the formula. =A0I am trying to remove the $ through a function =
key but
> > am not sure how to do it. =A0There are several of these that I need to =
do so I
> > want to avoid manually going and removing them.
> > Thank, =A0Chuck
>
> > =3DGETPIVOTDATA(" WristX2",$A$4,"Month",DATE(2008,9,1))+GETPIVOTDATA("
> > WristX2",$A$4,"Month",DATE(2008,10,1))+GETPIVOTDATA("
> > WristX2",$A$4,"Month",DATE(2008,11,1))+GETPIVOTDATA("
> > WristX2",$A$4,"Month",DATE(2008,12,1))+GETPIVOTDATA("
> > WristX2",$A$4,"Month",DATE(2009,1,1))+GETPIVOTDATA("
> > WristX2",$A$4,"Month",DATE(2009,2,1))
>
> > "David Heaton" wrote:
>
> > > On May 8, 12:31 am, Chuck W <Chu...@discussions.microsoft.com> wrote:
> > > > Hi,
> > > > I have a pivot table of data in a workbook called Pivot MT with uni=
ts as row
> > > > names and months as column names. I have a separate workbook called=
 Monthly
> > > > Totals in which I am referencing certain values inside the pivot ta=
ble. =A0I
> > > > want to use the fill handle in this Monthly Totals workbook and dra=
g accross
> > > > the columns to capture certain values in the pivot table. =A0Howeve=
r, there
> > > > seems to be an absolute values (i.e. $A$4) which causes all values =
across to
> > > > be the same. =A0I want to remove the absolute values but F4 doesn't=
 seem to
> > > > work. =A0Can someone help?
>
> > > > Thanks,
>
> > > Chuck,
>
> > > Can you send the complete formula you are trying to drag in the
> > > Monthly Totals sheet
>
> > > That may give us a better understanding of what the problem is.
>
> > > Regards
>
> > > David
>
> --
>
> Dave Peterson- Hide quoted text -
>
> - Show quoted text -

Chuck,

there is a menu option not usually shown on the Pivot Table bar call
'Generate Pivot Data'.
This affects how Excel sees the pivot table cells.

The default is on, but when you turn this off, instead of the
=3DGETPIVOTDATA formula (which doesnt allow relative references),
you will see normal cell references such as =3DC1, D1 etc.
You should then be able to drag your formula in the normal way to
increment the values.

Regards

David
0
dheaton (109)
5/9/2009 5:11:57 AM
Reply:

Similar Artilces:

Pivot Table Source Data
I have an existing Pivot Table that usually updates daily, albeit this relies someone in the office refreshing the data. Anyway we seem to have missed one days data and thought we would be clever and just include the days missing data back into the source data. In effect we are putting the 8th September data between the 7th and the 9th. However no matter what we do the data from the 8th ends up as the last column in the pivot table. Are we missing something really simple here or has my brain turned to mush? thanks for the help in advance. Bryan P.S I also tried creating a dynamic range but ...

Grouping dates in pivot table
I am pulling data from a SQL Server database to create a pivot table. Excel (07) is not recognizing the field as a date. This is a field I would like to group by in the pivottable. I am aware of all the techniques to convert this to a date field, but I am searching for an answer as to why XL pivot tables cannot consume the dates directly from a sql query. I have played with bringing the dates back in a number of differenent formats with no success. Any insights into this would be appreciated. Probably coming in as text and yuo may only need to copy an unused cell and paste sp...

Sales Distribution Entry table
Can someone point me at the table that stores the data for the Sales Distribution Entry window? Thanks. On Feb 27, 10:44=A0am, ALGP <gpjef...@gmail.com> wrote: > Can someone point me at the table that stores the data for the Sales > Distribution Entry window? > > Thanks. SOP10102 ...

pivot table grand totals not showing for all columns...
i have a pivot table that shows the grand total for some columns, but not for others... there are no differences in the data layout, yet this happens? any clues as to why? and how I can get the grand totals to show for all fields. thanx in the PivotTable floating toolbar, click PviotTable, then Table Options, select 'Grand totals for columns' option. ...

Removing RAID controller at XP boot up
I installed a pci card with two ATA 133 HD drive ports it came with drivers, I think I installed the wrong driver (RAID instead of straight). Cant get rid of the Marvel Raid control because it comes up before boot and has presumably tucked itself in the bios somewhere. Is there a solution? Perhaps reflashing the bios? Charlie+ "Charlie+" <charlie@xxx.net> wrote in message news:k47mq55qel8a848nl8u1l7rp31uvvdhg4m@4ax.com... > I installed a pci card with two ATA 133 HD drive ports it came with > drivers, > I think I installed the wrong driver (RAID inste...

Cannot enable Blind copy option (BCC)
Have this option on my computer at work- use it all of the time. I have outlook 2002 and no matter what I do I cannot see the BCC button- it is not in the view section- it is not in the options section- it is not anywhere. It is almost annoying enough to make me switch to eudora. Why make it so i can't find or use it? Please help When in Outlook XP (2002) I open a new mail message... In the menu of that new message click VIEW There is an option to check "BCC Field" - check it. Enjoy, Terry "kentg@earthlink.net" <anonymous@discussions.microsoft.com>...

Exchange 5.5 MTA Event-ID 290 after site removal
Hi, hopefully someone can help me. We have Exchange 5.5 Org (most SP4) – 1 hub site and 5 spoke sites. In our hub site we have 1 connector server and one mailbox server. Due to Exchange server consolidation, I removed one site (KB324340) – same procedure as I did it three times before - Now I’m getting every 4 hours on the bridgehead server in the hub site the following event-id, which points to the old and removed server/site: MTA Event-ID 290 – NDR,.. A non-delivery report (reason code unable-to-transfer and diagnostic code unrecognised-OR-name)) is being generated for message message. ...

Data Tables
In Excel 2000, must data tables (Data\Tables...) always be located on the same worksheet tab as the source data? I want to put summary tables into a separate worksheet (tab), but Excel won't let me do that. Steve Looks that way, at least in Excel97 (my ver) But we could always mirror the data tables (or parts thereof) elsewhere (eg: another sheet) via simple link formulas -- Rgds Max xl 97 --- Singapore, GMT+8 xdemechanik http://savefile.com/projects/236895 -- "Steve D" <sdrenker_(you_know_what_here)_media.sj.nec.com> wrote in message news:ufhMsZfyFHA.3804@TK2MSFTNGP10....

Formatting number in Data Table
I am using Excel 2007. I have a spreadsheet that the figures can be negative or positve. I have formatted the cells with this custom format $#,##0;[Red]$-#,##0. On the spreadsheet the format works fine. I created a chart from the data. I have a data table at the bottom of the chart. The problem is that the format from the spreadsheet is not reflected in the data table. When I click on Format Data Table, there is no option for number formatting. There is a number formatting option available when i click Format Axis, but this doesn't change it, because the actual axis is Text. Is th...

Copying Data From SQL Into Excel
When I copy a range of data from SQL and paste it into Excel, the data doesn't appear to be available for formulas - in this case a VLOOKUP formula. However, once I click in the formula bar (as if to edit the data) then hit "return" the data is "magically" available for the VLOOKUP formula. It seems to me to be a format problem, but changing the format of the data doesn't help. Could this be related to similar issues when copying data from Access into Excel? Are there any workarounds? It sounds similar to the Access problem. Instead of pasting, you can ...

Pvt Table Question
I am creating several pvt tables that will be updated often with new data. I want to know if there is any function within a pvt table that I can use to set up my pvt table so that only the top 10 data points are automatically displayed. So for example, if i have pvt table set up to show brand name and then accounts for each brand name and I sorted on accounts descending and only wanted the pvt table to show the top 10 brands that had the highest total accounts. Is this possible? Thanks. Hi, To show the top 10 values for a field - double-click the field heading, click 'Advanced' ...

Table name length in microsoft query
Hi! Is it true that the maximum length of a tablename is still 8 characters. I'm useing Excel 97 and trying to get data from a Paradox 9 table via odbc-link and Microsoft Query. I have heard a few years ago that long names are OK and supported all over the microsoftian regime. Is there any trick to avoid this without shortening all the names. Greetings from Helsinki HK ...

Pictures removed from sent emails
I've always been able to paste pictures (such as screen shots) from the clipboard within the body of HTML emails, but something's happened and now when I paste a picture it gets removed before it's sent. I can see the picture in the email when I'm writing it, but after clicking Send and opening the email in my Outbox before sending, there's just a blank empty frame with the message "The linked image cannot be displayed. The file may have been moved, renamed or deleted. Verify that the link points to the correct file and location." and the little &...

copy and match data from one worksheet into another via script?
Hi all I have two worksheets 2 columns each The first sheet has ID and Name Column for example: Code ------------------- ID[/B] NAM 193948 Michael 684588 John 535279 Luke 098734 Matthe ------------------- my 2nd sheet Code ------------------- ID [B]NAM Michael John Luke Matthe ------------------- How do i use the first sheet to match up the names on the 2nd sheet and ultimately copy the ID numbers onto the 2nd sheet? This is just a simplified example. There are thousands of entries. Thank you if you have ...

Table relationships
I've created a database with about 15 tables for a participant data in a research study. Each participant will complete the 15 assessment measures. My ultimate goal is to create a data entry system whereby a research assistant can pull up a form and enter an ID number, plus the answers to the 15 assessment measures. I know I can create a query to combine all the tables, but it seems like the research assistant would have to enter the id and date 15 times (once for each table pulled in to the query). Is there a way to just enter the ID number one time and have it apply t...

copy and paste into excel
I am trying to set up a sheet into which i can copy and paste various items into a group of cells? and upon being pasted into the group the item will be automatically shrunk to fit the size of the cells it was pasted into, rather than expanding them to fits its size. Any ideas anyone?????? Hi, try copy data and paste them like: Edit/Paste Special/Values. It does not change the size of the columns. Marian Hi, What about 'format cells' 'Alignment' and check "shrink to fit"? That is under Text Control - is that any use??? Cheers, Mark copying+pasting into cell...

How do I Remove a Split from my Comments in Excel 2003?
I have set my current workbook to split/freeze the first column and first 2 rows. Now, when I add a comment to the second row (in any column) my comments are cut off if I should scroll down. I don't ever remember the behavior before. And I don't know what I've done to enable it but it's really annoying. How do turn this off. Instead of Window>Split try selecting B3 then Window>Freeze Panes to hold top 2 rows and left column in view when scrolling. Gord Dibben Excel MVP On Thu, 6 Oct 2005 10:54:01 -0700, JesusPresley <JesusPresley@discussions.microsoft.com>...

Copying sorksheet formatting
How do I copy worksheet formatting, inc. custom headers, footers, column & row formatting across all the worksheets in a workbook? Hi one way: formating them all at the same time: - group the sheets (hold down the SHIFT key while selecting the sheets) - apply the format to a cell, column, row -- Regards Frank Kabel Frankfurt, Germany driverdriver wrote: > How do I copy worksheet formatting, inc. custom headers, > footers, column & row formatting across all the worksheets > in a workbook? You can also select the entire sheet with CTRL+A and do Edit / Copy, then group yo...

Copy and Paste question
I have a macro that copies and pastes a large amount of data. After it has run I get the question: " There is a large amount of information on the clipboard. Do you want to [keep it]?" Could someone please tell me how I can avoid this question being asked as I never wish to keep the data? Many thanks Insert this line after the pasting is done, it will clear the clipboard: Application.CutCopyMode = False hth knut egil "Richard" <rgarwell@jaguar.invalid> skrev i melding news:bpi1ee$cf71@eccws12.dearborn.ford.com... > I have a macro that copies and pastes a l...

Can I remove blanks from a range without using sort?
I have a range of cells A1:a10, say, which obtain data from another source. Some of those cells a3, a6:a8, say, under certain conditions, will be blank. How do I reorder this range such that the cells containing information are listed together, removing the blanks? I want to do this using a formula, rather than filter or sort, as the data, and hence the blank cells, will change, and I want to perform analysis on the cells containing data. this is from one of the newsgroup correspondents use this code statement Range("a1:a10").SpecialCells(xlCellTypeBlanks).EntireRow.Del...

copy setup and master to new compan
Hi to all – is any idea how to copy all setup and master tables from the existing company to a new company when I have already have an existing data on same server using Great Plains 8.0 , SQL-2000 and complete module of Great plains. thanks Here is a KB article with instructions: https://mbs.microsoft.com/knowledgebase/KBDisplay.aspx?scid=kb;en-us;872709 -- Victoria Yudin GP MVP "AFT" <AFT@discussions.microsoft.com> wrote in message news:5138A080-DC38-462C-8101-CE7D55915D0E@microsoft.com... > Hi to all - is any idea how to copy all setup and master tables from...

Adding a total row to a chart data table
I have the following data in a chart Week 1 2 3 4 5 6 7 8 Data1 1 5 7 6 1 6 7 6 Data2 3 4 5 7 9 4 6 6 Is it possible to have the data table show a totals row? I.E: Totals 4 9 12 13 10 10 13 12 Thanks in advance, John You can't include the total in the data table. As a workaround, you could embed the chart on a worksheet, and display the data below it. Tushar Mehta has instructions on his web site: http://tushar-mehta.com/excel/newsgroups/data_table/index.htm John Ortt wrote: > I have the following data in a chart > > Week 1 2 3 4 5 6 7 8 > Data1 1 5 7 6 1 6 7 6...

How to copy a filtered range ?
Hi, In a range of cells that i have filtered by edit>go>special>formulas>ctrl+9, therefore remained only rows with constant values, now that i need to copy these rows on a new book to upgrade their values and repaste them on the original book in the same column but without overwriting the hidden rows which contains formulas, is that possible ? Thank you very much . -- gaftalik ------------------------------------------------------------------------ gaftalik's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=6450 View this thread: http://www.excelfor...

pivot table 05-01-10
I have data on two worksheet and i want to analize data by consolidate them togeter.How can i use pivot table? -- meihua meihua;950632 Wrote: > I have data on two worksheet and i want to analize data by consolidate > them > togeter.How can i use pivot table? > > -- > meihua Option 1. Yes, you can use mutliple sheets connected to single pivot. choose, Multiple consolidation ranges option in Pivot table and Pivot Chart Wizard - step 1 of 3. Option 2. It is better to get the all data into single sheet and use pivot. thanks bala --...

Windows Malicious Software Removal Tool
Just a quick question, I am wondering if anyone else is having problems downloading Windows Malicious Software Removal Tool. I can download/install it from the Windows Update site but, it will not install using Automatic Updates. This is obviously not a serious problem but, I would like to solve it if possible. Rick Never such a problem here. Always state your full Windows version (e.g., WinXP SP3; WinXP 64-bit SP2; Vista SP1; Vista 64-bit SP2; Win7; Win7 64-bit) when posting in a forum or newsgroup. Please do so in your next reply. What anti-virus application or sec...