+ Reply to Thread
Results 1 to 9 of 9

Formulae based on references to an array changes when adding a row to pivot table

  1. #1
    Registered User
    Join Date
    06-28-2020
    Location
    Boston, MA USA
    MS-Off Ver
    2016
    Posts
    7

    Question Formulae based on references to an array changes when adding a row to pivot table

    Hello,

    I have a lookup in a pivot table (a1:c10) and lookup table d1:d10.

    row# A B C D E
    1 1 4 7 0 2
    2 2 6 0 1 1
    3 4 8 9 2 2
    4 3 5 9 3 4
    5 8 1 5 4 1
    6 2 6 0 5 4
    7 4 7 9 6 2
    8 1 5 0 7 1
    9 6 3 1 8 3
    10 7 8 2 9 3

    An array formula in e1:e10 is {=MIN(IF(D1=(Sheet1!A$1:C$10),ROW(A$1:C$10)))}
    This extracts the first occurrence of a number from d1:d10.

    The first occurrence of d1 (value 0) is in row 2 - hence e1 = 2.
    The first occurrence of d2 (value 1) is in row 1 - hence e2 = 1. So on and so forth.

    Everything looks fine so far.

    I want to add row to pivot table (a1:c10) in row#1 shifting all the rows to a2:c11 - without shifting d1:e10 and without changing the formula in e1:e10.

    The moment I add a row (say 5 4 9) in a1:c1 shifting rows down, the formula in e1:e10 changes to {=MIN(IF(D1=(Sheet1!A$2:C$11),ROW(A$2:C$11)))}.

    I do not want this formula to be updated when I add a row to pivot table. Somehow, I cannot control that no matter what I do!

    Please help! (Sounds simple - however, new to EXCEL - so just not been able to do that.)

    Thanks.

  2. #2
    Forum Guru
    Join Date
    03-02-2006
    Location
    Los Angeles, Ca
    MS-Off Ver
    WinXP/MSO2007;Win10/MSO2016
    Posts
    11,124

    Re: Formulae based on references to an array changes when adding a row to pivot table

    (How to) Upload a Workbook directly to the Forum
    (please do not post pictures or links to worksheets)
    • Click Advanced next to "Post Quick Reply" button at the bottom right of the editor box.
    • Scroll down until you see "Manage Attachments",
    • Click the "Choose" button at the upper left (upload from your computer).
    • Select your file, click "open", click "upload"
    • Once the upload is completed the file name will appear below the input boxes in this window.
    • Close the Attachment Manager window.
    • Click "Submit Reply"
    Note: Please do not attach password protected workbooks/worksheets
    Ensure to disable any Workbook Open/Autorun macros before attaching!
    Ben Van Johnson

  3. #3
    Registered User
    Join Date
    06-28-2020
    Location
    Boston, MA USA
    MS-Off Ver
    2016
    Posts
    7

    Re: Formulae based on references to an array changes when adding a row to pivot table

    Attached is the workbook for which I request the help.
    Just an FYI - it is EXCEL 2016.
    Thanks.
    Attached Files Attached Files

  4. #4
    Forum Guru
    Join Date
    03-02-2006
    Location
    Los Angeles, Ca
    MS-Off Ver
    WinXP/MSO2007;Win10/MSO2016
    Posts
    11,124

    Re: Formulae based on references to an array changes when adding a row to pivot table

    Create a dynamic named range
    pt_data
    refers to: =OFFSET(Sheet1!$D$1,0,-3,COUNT(Sheet1!$C:$C),3)

    Then the formula in column E is:
    Please Login or Register  to view this content.

  5. #5
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    6,745

    Re: Formulae based on references to an array changes when adding a row to pivot table

    Try
    Please Login or Register  to view this content.

  6. #6
    Registered User
    Join Date
    06-28-2020
    Location
    Boston, MA USA
    MS-Off Ver
    2016
    Posts
    7

    Re: Formulae based on references to an array changes when adding a row to pivot table

    Wow! What a wonderful solution.

    With my few tests, this seems to be working fine.

    How do I add '******' to your answer protonLeah?

    Thanks, again.

  7. #7
    Registered User
    Join Date
    06-28-2020
    Location
    Boston, MA USA
    MS-Off Ver
    2016
    Posts
    7

    Re: Formulae based on references to an array changes when adding a row to pivot table

    Hello bebo021999, let me try your solution, too. Thanks for your reply to help me.

  8. #8
    Forum Guru
    Join Date
    03-02-2006
    Location
    Los Angeles, Ca
    MS-Off Ver
    WinXP/MSO2007;Win10/MSO2016
    Posts
    11,124

    Re: Formulae based on references to an array changes when adding a row to pivot table

    Are you saying that you want a number followed by six stars like: 3******?

  9. #9
    Registered User
    Join Date
    06-28-2020
    Location
    Boston, MA USA
    MS-Off Ver
    2016
    Posts
    7

    Re: Formulae based on references to an array changes when adding a row to pivot table

    I want to thank you and really appreciate an excellent solution to my problem - suggesting the dynamic named range!

    I have added to the reputation for your profile.

    Once again - thanks a lot.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. How to evaluate data in a pivot table with formulae
    By opusarlo in forum Excel General
    Replies: 1
    Last Post: 11-21-2017, 01:47 PM
  2. Replies: 2
    Last Post: 11-21-2017, 12:34 PM
  3. Replies: 7
    Last Post: 02-27-2017, 07:41 AM
  4. Create pivot table based on array in memory
    By JasperD in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 01-22-2015, 11:41 AM
  5. Summarising Data with a Pivot Table and Formulae
    By andrewc in forum Excel General
    Replies: 4
    Last Post: 06-27-2014, 06:08 AM
  6. Formulae within Pivot Table Report?
    By Njohn1313 in forum Excel General
    Replies: 1
    Last Post: 06-05-2014, 04:18 PM
  7. Adding a % to aggregated data in pivot table based on row
    By cubmar in forum Excel Charting & Pivots
    Replies: 0
    Last Post: 10-28-2012, 10:11 PM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1