Here's the short and skinny,

Created a Name with a dynamic range

=OFFSET(Data!$A$1,0,0,COUNTA(Data!$A:$A),COUNTA(Data!$1:$1))

In my pivot table, i set the source as the dynamic range name

whenever I upload to sharepoint and open the document, it refreshes the pivot fine. If i save it, open it, and refresh the pivot it breaks with the error

"Cannot open pivottable source file"

It is placing the filename in the source of the pivot table so instead of:
DataRange i get 'FileName.xls'!DataRange.

Is there anyway to make the DataRange name absolute in the source file?