Updating Multiple Pivot Ranges
 

Please help:


I have about 330 pivots (yeah, it's a lot) on six different tabs whose
source data (range) is located in two different files.


My Excel crashed and after I accepted the auto-save updates, the range
addresses changed from being located in the same folder (where my
pivots are located, [Filename.xls]Sheet1!$A$1:$ZZ$62000) to being the
entire address (C:\Documents and Settings\user\applications\etc...).


I tried in Edit-Links to 'relink' the correct files; however, when I
re-open the file, it continues to reference the entire address.


How can I effeciently change all of the range addresses?


Thanks,


Joshua

I would have guessed that Edit|Links would have worked. You're sure you saved
after you did that change?

Maybe you can cheat.

Move that file that is pointed to to:
C:\Documents and Settings\user\applications\etc.....

Then open your file--the links will point to the now correct location.

Now open that file in:
C:\Documents and Settings\user\applications\etc....

And save it where you want it to really be.
then close that workbook.

Did it work?


SJJoshua wrote:

Please help:

I have about 330 pivots (yeah, it's a lot) on six different tabs whose
source data (range) is located in two different files.

My Excel crashed and after I accepted the auto-save updates, the range
addresses changed from being located in the same folder (where my
pivots are located, [Filename.xls]Sheet1!$A$1:$ZZ$62000) to being the
entire address (C:\Documents and Settings\user\applications\etc...).

I tried in Edit-Links to 'relink' the correct files; however, when I
re-open the file, it continues to reference the entire address.

How can I effeciently change all of the range addresses?

Thanks,

Joshua

--

Dave Peterson

Thanks, Dave,

I tried cheating, whereby I did exactly what you suggested. For my data
that wasn't contained in a pivot, updating the Edit|Links worked fine.
However, the range addresses for the Pivots remained constant.

Is there any way to look at the code of the Pivots to do a 'Find
All/Replace' type thing? I certainly hope that what I'm trying to do is
possible...

Thanks!

"Dave Peterson" wrote:

I would have guessed that Edit|Links would have worked. You're sure you saved
after you did that change?

Maybe you can cheat.

Move that file that is pointed to to:
C:\Documents and Settings\user\applications\etc.....

Then open your file--the links will point to the now correct location.

Now open that file in:
C:\Documents and Settings\user\applications\etc....

And save it where you want it to really be.
then close that workbook.

Did it work?


SJJoshua wrote:

Please help:

I have about 330 pivots (yeah, it's a lot) on six different tabs whose
source data (range) is located in two different files.

My Excel crashed and after I accepted the auto-save updates, the range
addresses changed from being located in the same folder (where my
pivots are located, [Filename.xls]Sheet1!$A$1:$ZZ$62000) to being the
entire address (C:\Documents and Settings\user\applications\etc...).

I tried in Edit-Links to 'relink' the correct files; however, when I
re-open the file, it continues to reference the entire address.

How can I effeciently change all of the range addresses?

Thanks,

Joshua

--

Dave Peterson

Not that I know.

Maybe just changing the source through the wizard would be easiest.

SJJoshua wrote:

Thanks, Dave,

I tried cheating, whereby I did exactly what you suggested. For my data
that wasn't contained in a pivot, updating the Edit|Links worked fine.
However, the range addresses for the Pivots remained constant.

Is there any way to look at the code of the Pivots to do a 'Find
All/Replace' type thing? I certainly hope that what I'm trying to do is
possible...

Thanks!

"Dave Peterson" wrote:

I would have guessed that Edit|Links would have worked. You're sure you saved
after you did that change?

Maybe you can cheat.

Move that file that is pointed to to:
C:\Documents and Settings\user\applications\etc.....

Then open your file--the links will point to the now correct location.

Now open that file in:
C:\Documents and Settings\user\applications\etc....

And save it where you want it to really be.
then close that workbook.

Did it work?


SJJoshua wrote:

Please help:

I have about 330 pivots (yeah, it's a lot) on six different tabs whose
source data (range) is located in two different files.

My Excel crashed and after I accepted the auto-save updates, the range
addresses changed from being located in the same folder (where my
pivots are located, [Filename.xls]Sheet1!$A$1:$ZZ$62000) to being the
entire address (C:\Documents and Settings\user\applications\etc...).

I tried in Edit-Links to 'relink' the correct files; however, when I
re-open the file, it continues to reference the entire address.

How can I effeciently change all of the range addresses?

Thanks,

Joshua

--

Dave Peterson


--

Dave Peterson