After searching constantly for a solution to the Reference Not Valid error when using Pivot Tables, I finally found the solution. It may have previously been found, but because I had trouble finding it in so many search results I wanted to post it here.

If you refresh your pivot and you get the "Reference Not Valid" error and you are using a named range for your referenced data array, you must delete the named range and re-add it to the list.

That simple.

This is because the file is somehow referencing an external data range that has been deleted. This happened to me when creating a file in Excel 2003, making adjustments to it in Excel 2007, then re-opening it in Excel 2003 again.

Hope this helps.