Help with 2007 pivot tables loosing connection to data

Pivot tables are something completely new to me. This is my first attempt at using them to create charts.

I have a workbook with 3 worksheets
1-Data - Contains the data that is used to create the Pivot tables - New data rows are added to this sheet daily - It has a Range named "ChartRange" that expands each time a row is added.
2-ChartData - Is where I build all the Pivot tables - All the Pivot tables are built using the "ChartRange" as the source. All are simple tables using sum and average
3-Charts - Contains all the charts built on the Pivot Tables. Each chart was created on the same page as the tables and moved to the chart sheet.

When the table and charts are created everything works fine. I can add rows to the "Data" worksheet and when I go look at the "ChartData" sheet everything is updated like expected. The Charts are also updated correctly.
I can then add more rows to the "Data" sheet and everything is still fine.

If I save and exit Excel. When I startup again, go to "ChartData" all the Pivot Tables have the Row and Column Labels but no Data. Refresh does nothing, the source data is still set to "ChartRange"

Web searches so far has indicated that Column Heading changes or blank cells could cause this. But no changes were made to the column headings, just added rows. Also all cells have content, no blank cells.

Anyone have any Ideas of what I am doing wrong.

I seem to recall that 2007 had a few issues with named ranges that started with the word "Chart" so I'd start by just trying a different name.

Thanks for the quick replay

I just changed the range name to "TableData" but It did not fix the problem.

I built a simple pivot table using the new range everything looked fine. I did not make any changes to the "Data" Worksheet
I saved the workbook and exited excel
I restarted the workbook and found that the pivot table was blank. Labels were still there but no data

I think I am narrowing it down

In my Pivot Tables My column are months - Jan Feb Mar April
Average Per Month 123 245 342 334

But for some reason it also has a month 0 after April, I have no blanks in my data so I do not know where it comes from.
To remove this I am using a Filter on months to remove it. Label filter on Month "Greater than 0. Witch removed the Month 0 and leaves All selected in the list. which means any new month would be included automatically

What I found was that when I remove this filter everything works correctly. I am able to exit excel and return with everything intact.
So I am guessing that I am doing the filter incorrectly.

Is there another way of getting rid of Month 0

In the Data tab of the pivot table options, make sure that the number of items to retain per field is None, and then refresh the pivot table. Does the 0 still appear?

rorya

The 0 did still appear after refresh
But I did figure out where it was coming from. The first row of the data had 0's for the month day and year. Changed these to the same as the next row and the 0 went away.
Still don't know why filtering it out on the Pivot Table did not work.

Thanks to all

Could be a bug. 2007 was, in my opinion, the second worst version of Excel in living memory. You need to be on Service Pack 3 at least for it to be usable.

Rorya

I did look, it is SP3. Its on my work computer and they do not seem to want to upgrade. So I will have to live with it for awhile.

Thank you