Formula fill handle doesn't auto-fill properly when data column is part of pivot table

VBAProIWish

Well-known Member
Joined
Jul 6, 2009
Messages
1,027
Office Version
  1. 365
Platform
  1. Windows
Hello All,

I'm trying to use the Auto-Fill handle to copy a formula down a column and it's not filling the formula down each row incrementally. Instead, it's copying the EXACT formula using row data from the original location of the formula.

In the example below, the formula I used for the first row with "Blue" (highlighted in green) says "=Amount x total" or..

Code:
=B3*GETPIVOTDATA("COLOR",$A$1,"COLOR","Blue","Amount",100)

so, the formula for the "Blue" row is correct. But when trying to use the fill handle to copy the formula to the "Dark Blue" row (highlighted in red), the formula is still basing its data on the "Blue" row and NOT the "Dark Blue" row as it should. Here's the formula it copied down to the "Dark Blue" row..

Code:
=B4*GETPIVOTDATA("COLOR",$A$1,"COLOR","Blue","Amount",100)



However, if I do the "Dark Green" row manually by putting in the formula

Code:
=B5*GETPIVOTDATA("COLOR",$A$1,"COLOR","Dark Green","Amount",100)

The formula works correctly (without using the auto-fill).

Does anyone know what's wrong here?


<TABLE style="WIDTH: 220pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=295 x:str><COLGROUP><COL style="WIDTH: 91pt; mso-width-source: userset; mso-width-alt: 4323" width=122><COL style="WIDTH: 55pt; mso-width-source: userset; mso-width-alt: 2616" width=74><COL style="WIDTH: 26pt; mso-width-source: userset; mso-width-alt: 1251" width=35><COL style="WIDTH: 48pt" width=64><TBODY><TR style="HEIGHT: 13.8pt" height=18><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 91pt; HEIGHT: 13.8pt; BORDER-TOP: black 0.5pt solid; BORDER-RIGHT: black 0.5pt solid" class=xl24 height=18 width=122>COLOR</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: transparent; WIDTH: 55pt; BORDER-TOP: black 0.5pt solid; BORDER-RIGHT: black 0.5pt solid" class=xl24 width=74>Amount</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: transparent; WIDTH: 26pt; BORDER-TOP: black 0.5pt solid; BORDER-RIGHT: black 0.5pt solid" class=xl24 width=35>Total</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: black 0.5pt solid; BORDER-RIGHT: black 0.5pt solid" class=xl25 width=64></TD></TR><TR style="HEIGHT: 13.8pt" height=18><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 13.8pt; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl24 height=18>Blue</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: transparent; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl25 align=right x:num="100">$100.00</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: transparent; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl24 align=right x:num>1</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: transparent; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl26 align=right x:num="100">$100.00</TD></TR><TR style="HEIGHT: 13.8pt" height=18><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 13.8pt; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl24 height=18>Dark Blue</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: transparent; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl25 align=right x:num="100">$100.00</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: transparent; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl24 align=right x:num>3</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: transparent; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl27 align=right x:num="100">$100.00</TD></TR><TR style="HEIGHT: 13.8pt" height=18><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 13.8pt; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl24 height=18>Dark Green</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: transparent; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl25 align=right x:num="100">$100.00</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: transparent; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl24 align=right x:num>3</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: transparent; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl25 align=right x:num="300">$300.00</TD></TR><TR style="HEIGHT: 13.8pt" height=18><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 13.8pt; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl24 height=18>Dark Red</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: transparent; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl25 align=right x:num="100">$100.00</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: transparent; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl24 align=right x:num>3</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: transparent; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl25></TD></TR><TR style="HEIGHT: 13.8pt" height=18><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 13.8pt; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl24 height=18>Green</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: transparent; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl25 align=right x:num="100">$100.00</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: transparent; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl24 align=right x:num>1</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: transparent; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl25></TD></TR><TR style="HEIGHT: 13.8pt" height=18><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 13.8pt; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl24 height=18>Light Blue</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: transparent; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl25 align=right x:num="100">$100.00</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: transparent; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl24 align=right x:num>2</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: transparent; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl25></TD></TR><TR style="HEIGHT: 13.8pt" height=18><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 13.8pt; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl24 height=18>Light Green</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: transparent; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl25 align=right x:num="100">$100.00</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: transparent; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl24 align=right x:num>2</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: transparent; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl25></TD></TR><TR style="HEIGHT: 13.8pt" height=18><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 13.8pt; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl24 height=18>Light Red</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: transparent; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl25 align=right x:num="100">$100.00</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: transparent; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl24 align=right x:num>2</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: transparent; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl25></TD></TR><TR style="HEIGHT: 13.8pt" height=18><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 13.8pt; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl24 height=18>Red</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: transparent; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl25 align=right x:num="100">$100.00</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: transparent; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl24 align=right x:num>1</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: transparent; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl25></TD></TR></TBODY></TABLE>
Thanks
 

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result
Do one of two things: don't use GETPIVOTDATA function, or, when using it, reference the first column that contains the string. String literals won't get auto-filled like you intended.
 
Upvote 0
iliace (or anyone else)

Is there a "Sub" equivelant that will do this for me? I'm really looking for code to automate this.

If not, could you provide an alternative formula that will fill down like I want it to?

Thanks very much for the reply iliace



Thanks
 
Upvote 0
I don't understand the question. This can be dragged down:

=B4*GETPIVOTDATA("COLOR",$A$1,"COLOR",A4,"Amount",100)
 
Upvote 0
If I drag down from dark blue to dark green, the total is still "100", it should be "300". I'm sure that I'm missing something here, I just don't know what it is.

Thanks
 
Upvote 0
Well, I certainly have no idea since I can't see it.
 
Upvote 0
iliace,

You are 100% correct! I must've entered it in wrong the first time, so this time I copied your formula and dragged it down. Works beautifully! Exactly what I wanted!

Thank you thank you!
 
Upvote 0

Forum statistics

Threads
1,213,538
Messages
6,114,217
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top