Keeping Source Files Open while Refreshing/Using Pivot Tables

Question

I have a situtation where I have to keep an Excel file open when I refresh the pivot tables in the other files tied to the data in that file.  This can be a problem when more than one person needs to use the source file.  What can I do to avoid this?--i.e. Are there settings, tools, etc. that I could use to eliminate this annoying problem?  If I refresh the pivot tables in the files without opening up the file with the source data, my computer freezes up.  Please advise...

Thanks,

Rick

Answer 1

Thanks Shane, It's worked for me :)

Answer 2

Hi Shane:

Saving the source file to the C Drive, closing it out, and then refreshing the pivot yielded the same results.  So I don't think it is a path problem.  I did not save the pivot table as you suggested.

Do you have any other ideas?

Thanks,

Rick

@ Rick: Done this also, and kept getting the result you are getting...

@ Shane: Your suggestion is correct though, but I think it applies when referencing/lookup a cell to another sheet in another excel file.

I've been googling this problem and I can't seem to find an answer. :(

Answer 3

I can find no other instances of this problem.  So here is an additional question/test.  Does this problem happen on every machine?

here is a post although not very helpful might suggest a direction:

http://social.msdn.microsoft.com/Forums/en-US/innovateonoffice/thread/759cb506-93c5-4a5e-9472-aeba13f8edc4

basically it suggest you should post to here

http://social.msdn.microsoft.com/Forums/en-US/Exceldev

because the Microsoft people that work there are allowed to test maybe?  Most of the people there are here but maybe Microft allows there people on that site to do things they are not allowed to do here.  It's worth a try.

---

If this answer solves your problem, please check, Mark as Answered. If this answer helps, please click the Vote as Helpful button. Cheers Shane Devenshire

Answer 4

Hi Shane:

Saving the source file to the C Drive, closing it out, and then refreshing the pivot yielded the same results.  So I don't think it is a path problem.  I did not save the pivot table as you suggested.

Do you have any other ideas?

Thanks,

Rick

Answer 5

Excel should handle refreshing against a closed file.  What is the full path to the second file?  Maybe it is too long for the pivottable to reference.  Try opening both files, and saving the data to the root of the C: drive.  Then close the data file and try the refresh.  If it works there is some problem with where the file is located.  Don't save the pivot table file (because it has a reference to the C drive).

---

If this answer solves your problem, please check, Mark as Answered. If this answer helps, please click the Vote as Helpful button. Cheers Shane Devenshire