Pivot Table Filter Drop Down Value Change

[dflak]

The attached worksheet illustrates the problem. I have a data source (Cols A:C) an a simple pivot table built from the data source (Cols F:J). Cell G1 is a filter that filters the pivot table based on the Site Name.

Here is how to "break" the pivot table: Select a Site in the filter (Site 03, for example). Type in "Site XX." You will get a message stating, "No item of this name exists in the pivot table. Rename 'Site 03' to 'Site XX'?"

If you select OK, it reassigns Site 03 to Site XX in the drop down list. Now the only way to see data for Site 03 is to select Site XX.

When you do this manually, obviously you are not going to make the selection unless you have some extenuating circumstance. The issue I had is that I was running this exercise in code, looping through a list of sites. So the old site was there, and the new site was copied in. But the new site didn't exist in the data. So (since I had display alerts turned off), the code happily reassigned the new site drop down. So Site A on the list pointed to Site A and Site B pointed to Site A.

There are work-arounds and checks to see if the site exists in the data before setting the filter such as doing a COUNTIF on the source data.

I would like an additional safety net. Is there a way to turn this feature off? I can barely think of a circumstance where I would want to use it.