Results 1 to 7 of 7

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

This is a discussion on Formula fill handle doesn't auto-fill properly when data column is part of pivot table within the Excel Questions forums, part of the Question Forums category; Hello All, I'm trying to use the Auto-Fill handle to copy a formula down a column and it's not filling ...

  1. #1
    Board Regular
    Join Date
    Jul 2009
    Posts
    779

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

    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?


    COLORAmountTotal
    Blue$100.001$100.00
    Dark Blue$100.003$100.00
    Dark Green$100.003$300.00
    Dark Red$100.003
    Green$100.001
    Light Blue$100.002
    Light Green$100.002
    Light Red$100.002
    Red$100.001

    Thanks

  2. #2
    Board Regular iliace's Avatar
    Join Date
    Jan 2008
    Location
    Maryland, USA
    Posts
    2,934

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

    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.

  3. #3
    Board Regular
    Join Date
    Jul 2009
    Posts
    779

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

    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

  4. #4
    Board Regular iliace's Avatar
    Join Date
    Jan 2008
    Location
    Maryland, USA
    Posts
    2,934

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

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

    =B4*GETPIVOTDATA("COLOR",$A$1,"COLOR",A4,"Amount",100)

  5. #5
    Board Regular
    Join Date
    Jul 2009
    Posts
    779

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

    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

  6. #6
    Board Regular iliace's Avatar
    Join Date
    Jan 2008
    Location
    Maryland, USA
    Posts
    2,934

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

    Well, I certainly have no idea since I can't see it.

  7. #7
    Board Regular
    Join Date
    Jul 2009
    Posts
    779

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

    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!

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com