Can't insert PIVOT table after deleting worksheet with previous PIVOT table on it

[rod531]

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.

 

[RoryA]

Are you sure you specified the correct range when creating them the second time?

 

[rod531]

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

 

[RoryA]

Then perhaps step 3 is the problem. What headers did you use in the new columns?

 

[rod531]

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)?

 

[RoryA]

Select all the data and insert a pivot table as normal. That should pick up all the columns - just double check the range in the pivot table dialog to be sure.

 

[rod531]

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.

 

[RoryA]

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.

 

[rod531]

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

 

[RoryA]

I suggest you try using just the original columns to make a pivot table first. If that works, try again adding in one more column. Repeat until you get the error and you will know which one is causing the problem.