Pulling information from one spreadsheet into another spreadsheet.

5 pts.
Tags:
Excel worksheets
Microsoft Excel 2003
microsoft excel functions
We have two spreadsheets created and spreadsheet b has referenced totals from certain columns from spreadsheet a. The problem is that the spreadsheets are maintaned by 2 different people. And throughout the day spreadsheet a gets new information added to it, how can this information be brought into spreadsheet b without closing and reopening spreadsheet b.

Answer Wiki

Thanks. We'll let you know when a new response is added.

I have a similar situation between Access database and Excel spreadsheet. I liked the solution suggested by DoneThat. Could DoneThat please share the two macros by showing the code? Do I need to put a button on Excel to save the spreadsheet say after one hour or can it be done automatically? Also how do I put “Refresh” in Access or in Excel? . Will be looking forward to your solution. Thank you in advance

Discuss This Question: 2  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • DoneThat
    Put a macro in spreadsheet A that saves periodically (I.E. no need to close & reopen). Let spreadsheet B user refresh as needed or add a macro that refreshes periodically.
    830 pointsBadges:
    report
  • DoneThat
    Never had a need to do this but here's some guidance... Spreadsheet A: You need to check and resave periodically... So when Excel starts, store the current time as last time saved. Since you want to save after "X number" of worksheet changes, using the Worksheet_Change() event, subtract current time from last time saved, save if X minutes have elapsed. Set a reasonable period - say 5 minutes for small workbooks - longer for multimegabyte workbooks since the user will not be able to work while the book saves. If the saves will be lengthy, put a courtesy message on the screen for your user. Spreadsheet B: You need to refresh (I.E. re-execute your external lookups.) Although you could set "a periodic refresh", I don't think that's good. Refreshing data without notifying the user could be pretty unsettling. Instead, I'd put a button on the sheet for the user to refresh as needed. A quick look on the Ozgrid site shows both "calculate" and "refresh" methods are available. One of those should do the trick. There is also an "OnTime" method available to the application but again, that would do something without the user's permission or preparation. I would not do that. Gary
    830 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following