remove Absolute references when copying from pivot table

[Chuck W]

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,

 

[David Heaton]

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 acrossto
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

 

[Chuck W]

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))

 

[Dave Peterson]

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.

 

[David Heaton]

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 inany
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.









--

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
=GETPIVOTDATA formula (which doesnt allow relative references),
you will see normal cell references such as =C1, D1 etc.
You should then be able to drag your formula in the normal way to
increment the values.

Regards

David