Pivot Table "Field Name is not valid" error

[iamageneralist]

I am trying to resolve an error when I try to set up a Pivot Table. The error
says that the field name in not valid. (And yes I have read to "pat"
Microsoft answer that it is because of a blank cell in the header.)
Points that may help:
There are no blank cells in the header row.
There are no hidden columns.
The range is not adding a blank row or column below or at the end.
The dynamic name range works in other instances such as a regular chart.
I can "Go To" the range and it highlights the correct data.
I can open the Insert/Name/Define dialog box and click in the middle of the
range definition and it highlights the correct data.
I had several Pivot Tables working with this data range, each on separate
sheets, but as I tried to add some additional ones I started getting this
message so I deleted all of the worksheets with Pivot Tables and tried
starting over and now it will not allow me to create any.

 

[Roger Govier]

Hi

Can any of your header Names be confused with cell References?
AC1, AC2 etc.

If so, change them to AC_1, AC_2
This problem is more pronounced with XL2007 with 3 letter column headings.

 

[iamageneralist]

Thanks for the idea Roger but that was not it.

I did not have any header names like AC1 but I did have some two and three
letter names. I changed them to something that could not be confused with a
cell reference and it did not change or eliminate the problem. I am still
getting the error message.

A little more information, I am using XL2003.

I have tried entering the range in several ways: just the range name, the
spreadsheet name followed by the range name, the workbook name followed by
the range name – none have worked.

If I select the sample set of data that is currently “in†that range, I can
create a pivot table with that data, however, if I set up the table as noted
and then try to change the source to the named range it gives me the same
error message.

Any other ideas?

 

[Roger Govier]

Hi

If you want to mail me a copy of the workbook, I will be pleased to take a
look.
To mail direct
roger at technology4u dot co dot uk
Make the obvious changes to at and dot to make valid email address.

 

[iamageneralist]

Roger,
Thank you for the offer to take a look at it.

I have, for the time being, resolved the error although I am more than a
little bit troubled about what "unlocked" it.

I had two columns out of the 56 that had headers but no content.
I dropped content into them and then updated the Pivot Table source from the
static data to the dynamic named range and it worked instead of giving me the
error message.

It troubled me that a blank in a data cell (this template has 56 columns
and potentially thousands of rows) would break the whole thing so I
experimented.

I deleted the content of those two columns so that they were again blank and
deleted the pivot table. I then closed the spreadsheet and opened it up with
the blank data in the two columns.
I then tried creating a pivot table and this time it allowed me to create it
based on the dynamic named range even though I had blank data cells within
the range and it even allowed me to build the pivot table on the columns that
had no data in them.
Since then I have built about 5 or 6 pivot tables each on different
spreadsheets within the workbook and on different columns of data within the
range but all off of that same range. The exact same range that previously
would not get past the error message.

A concern that I have is that I am building a template for use by 39
counties and I am uneasy about something that is inconsistent, working one
time but the same thing not working another time.

If anyone has any ideas on what the underlaying issues are here I would
appreciate a post.