Error when refreshing Pivot Table and using OFFSET function: "Reference is not valid error message."

Question

Original Title:"problem with named range in excel mac"

I have a named range in Excel Mac which I am using for Pivot tables.

When I try to refresh the pivot table or create a new table using this named range I am getting a "Reference is not valid error message."

I can create a pivot table using the same range of data without the named range, but am totally stumped by the reference not valid error message.

the named range is defined by the formula

=OFFSET(A1,0,0,COUNTA(A:A),17)

I am using the COUNTA(A:A) to make the database dynamic as column A requires input)

If I simply highlight the cells for the range and create a new pivot table it works fine, but as soon as I name the range I am getting a reference is not valid error message. This seems to happen even with a simple named range like =A1:Q10

Can anyone explain this to me?

Answer 1

Looks like a bug. And, for the record, upper or lower case makes no difference.

Answer 2

I have tried both of these:

=offset(Sheet1!$a$1,0,0,counta(Sheet1!$a:$a),17)

and

 =OFFSET(Sheet1!$A$1,,,COUNTA(Sheet1!$A:$A),17)

As soon as I use the named range I get the "Reference is not valid" error message.

If i just use the range in the pivot table it works with no problems

Answer 3

Does the named range refer to the specific sheet your data is on, e.g.:

=OFFSET(Sheet1!$A$1,,,COUNTA(Sheet1!$A:$A),17)

or is it relative?

Is it a workbook-level name rather than a worksheet-level name?

Answer 4

I had tried this, for some reason I still get an error, even if I make it an absolute range of =$A$1:$Q$10 instead of the dynamic range.

This is the strangest problem I have come across as the pivot table works perfectly fine without the named range.

Answer 5

Try making you references absolute, rather than relative:

=offset($a$1,0,0,counta($a:$a),17)

and see if this makes a difference.