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

1. ## 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. ## 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
• 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).
• Once the upload is completed the file name will appear below the input boxes in this window.
• Close the Attachment Manager window.
Ensure to disable any Workbook Open/Autorun macros before attaching!

3. ## 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.

4. ## 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. ## 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. ## 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.

Thanks, again.

8. ## 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. ## 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!

Once again - thanks a lot.

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

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