Hello. I have a worksheet with my raw data on. I successfully created two PIVOT tables on new worksheets. I then deleted the worksheets with the PIVOT tables on because I wanted to start afresh. Excel won;t let me insert a new PIVOT table now. I get the error message "The PIVOTTable field name is not valid. To create a PIVOT Table report, you must use data that is organised as a list etc" That error messages seems irrelevant and wrong - my data was fine for a PIVOT table before, I haven;t changed it. It is simply that I deleted a worksheet with a PIVOT table on. I am bewildered. Thanks in advance for any insights you may have. My issue has nothing to do with columns or column headers or lists or empty headers.
-
If you would like to post, please check out the MrExcel Message Board FAQ and register here. If you forgot your password, you can reset your password.
Can't insert PIVOT table after deleting worksheet with previous PIVOT table on it
- Thread starter rod531
- Start date
Upon further review, I think the sequence of events was as follows:
1. I created the worksheet with the raw data on
2. I created the two PIVOT tables on new worksheets
3. I added some extra columns in the worksheet with the raw data on
4. I deleted the worksheets with the PIVOT tables on
5. I tried to create a new PIVOT table and got the error message
If I go to an earlier file version which still has the two PIVOT tables in, and try to create a new PIVOT table for the amended data source worksheet (ie. from the data source with new columns in it), it allows me to create a third PIVOT table but the PIVOT table refers to old column headers and old data, not the new layout of the source data sheet.
Thanks
1. I created the worksheet with the raw data on
2. I created the two PIVOT tables on new worksheets
3. I added some extra columns in the worksheet with the raw data on
4. I deleted the worksheets with the PIVOT tables on
5. I tried to create a new PIVOT table and got the error message
If I go to an earlier file version which still has the two PIVOT tables in, and try to create a new PIVOT table for the amended data source worksheet (ie. from the data source with new columns in it), it allows me to create a third PIVOT table but the PIVOT table refers to old column headers and old data, not the new layout of the source data sheet.
Thanks
Upvote
0
I just used simple narrative headers like "Total Increase" and "Total Payment"
How about I posed my issue in a different way....
1. I created a data source worksheet
2. I created two PIVOT tables from it, on separate worksheets
3. I added new columns to the data source worksheet
What do I need to do in order to create a new PIVOT table which correctly picks up the new contents of the amended data source worksheet? (instead of the new PIVOT table somehow referring to the old data still)?
How about I posed my issue in a different way....
1. I created a data source worksheet
2. I created two PIVOT tables from it, on separate worksheets
3. I added new columns to the data source worksheet
What do I need to do in order to create a new PIVOT table which correctly picks up the new contents of the amended data source worksheet? (instead of the new PIVOT table somehow referring to the old data still)?
Upvote
0
If I reselect the right data area in the amended tab, Excel says "The PIVOTTable field name is not valid etc" and won't create a PIVOT table.
If I just click anywhere in the data and Insert PIVOT table, it the dialogue box shows the correct range that I want, but when the new PIVOT table worksheet opens, the PIVOT dialogue has all the old columns in it still.
I am an "amateur" at PIVOT tables but I have spent 3 days googling this now and my gut feel is that it may be a cache issue. I have read somewhere that when you insert a PIVOT table, Excel copies your source data to an invisible cache, so that when you manipulate the PIVOT table you always refer to the cache not to the actual source data worksheet. Consequently I feel like my Excel file has somehow got a cache of my old data somewhere in it and won;t let go of it. I don;t know how to sever the link.
I don't know what destruction I would cause if I tried to clear caches and whether it would affect any other files at the same time.
If I just click anywhere in the data and Insert PIVOT table, it the dialogue box shows the correct range that I want, but when the new PIVOT table worksheet opens, the PIVOT dialogue has all the old columns in it still.
I am an "amateur" at PIVOT tables but I have spent 3 days googling this now and my gut feel is that it may be a cache issue. I have read somewhere that when you insert a PIVOT table, Excel copies your source data to an invisible cache, so that when you manipulate the PIVOT table you always refer to the cache not to the actual source data worksheet. Consequently I feel like my Excel file has somehow got a cache of my old data somewhere in it and won;t let go of it. I don;t know how to sever the link.
I don't know what destruction I would cause if I tried to clear caches and whether it would affect any other files at the same time.
Upvote
0
RoryA
MrExcel MVP, Moderator
- Joined
- May 2, 2008
- Messages
- 42,309
- Office Version
- 365
- 2016
- Platform
- Windows
- MacOS
Are you using a table or regular range as the data source?
It sounds to me like one of the column headers is not valid for a pivot table field name. I see this quite a lot when someone has hidden a column for some reason and then added data to the right of it, and then tries to build a pivot table including the blank hidden column in the source data range.
It sounds to me like one of the column headers is not valid for a pivot table field name. I see this quite a lot when someone has hidden a column for some reason and then added data to the right of it, and then tries to build a pivot table including the blank hidden column in the source data range.
Upvote
0
Solution
I have repetition of column header names, but I have had that repetition right from the start and so the first PIVOT tables worked OK with it.
ie. Column W is Option 1, Column X is Inflation impact of Option 1, etc
then Column AM is Option 2, Column AN is Inflation impact of Option 2 etc
with a series of Options and related data listed across the table.
It is just a plain worksheet in Excel, no hidden columns, no named field range, just several columns and then some more columns inserted
ie. Column W is Option 1, Column X is Inflation impact of Option 1, etc
then Column AM is Option 2, Column AN is Inflation impact of Option 2 etc
with a series of Options and related data listed across the table.
It is just a plain worksheet in Excel, no hidden columns, no named field range, just several columns and then some more columns inserted
Upvote
0
Similar threads
- Question
