Share via

Pivot Table won't accept Dynamic Named Range as source

Anonymous
2018-01-31T11:32:53+00:00

I have my source data in a worksheet, like this, starting in A1.

COUNTRY CUSTOMER OCT NOV DEC JAN TOTAL
AFGANISTAN A
AFGANISTAN B
AFRICA C -420.44
AFRICA A 1666.00
AFRICA B
AFRICA C 226.79
ALBANIA A
ALGERIA B
ALGERIA C
ARGENTINA A
ARGENTINA B 209.57
AUSTRALIA C

I have a dynamic named range called "Data" using the formula =OFFSET('Country Data'!XEY147,0,0,COUNTA('Country Data'!$A$1:$A$1000),COUNTA('Country Data'!$A$1:$P$1)).

When I try to change the Data Source of my Pivot Table to use the range Data I get Reference is not Valid ! and an OK box.

I need my range to be dynamic so that users can insert columns or add rows & the data will be used in the pivot table.

I am using Office 2010 on a W10 pc. Users are on 2010 or 2013 on W7.

Microsoft 365 and Office | Excel | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

Answer accepted by question author

  1. Ashish Mathur 101.8K Reputation points Volunteer Moderator
    2018-01-31T23:47:44+00:00

    Hi,

    Convert your source data range into a Table.  When you add further rows/columns, just refresh your Pivot Table.

    3 people found this answer helpful.
    0 comments

Answer accepted by question author

  1. HansV 462.6K Reputation points MVP Volunteer Moderator
    2018-01-31T12:20:02+00:00

    Try changing the definition to

    =OFFSET('Country Data'!$A$1,0,0,COUNTA('Country Data'!$A$1:$A$1000),COUNTA('Country Data'!$A$1:$P$1))

    Remark: when you add one or more columns to the Data range, then refresh the pivot table, the new column(s) will be available, but they will not automatically be used in the pivot table.

    It would be better to restructure your data source like this:

    COUNTRY CUSTOMER MONTH AMOUNT
    AFGHANISTAN A JAN 234.56
    AFGHANISTAN A FEB -123.45
    ALBANIA B JAN 67.89
    ... ... ... ...

    That way, only the number of rows would change.

    2 people found this answer helpful.
    0 comments

2 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2018-02-01T09:03:33+00:00

    Hi,

    Convert your source data range into a Table.  When you add further rows/columns, just refresh your Pivot Table.

    Thank you Ashish my data is now both a table & a dynamic named range, over kill maybe :-).

    0 comments
  2. Anonymous
    2018-01-31T15:24:16+00:00

    Thanks Hans. I can see something strange had crept into my offset formula there. Thank you for the correction, it now accepts the range as the pivot table source.

    Unfortunately I cannot have the data in the format you suggest. (I noticed this was the norm for every single pivot table tutorial I looked at.)

    I will have to try & coach my users through the field settings.

    0 comments