Connect Files

D

Doug

I put this post under Gerneral Questions. I didn't get any good response so
I thought I would ask the experts.

I have 2 files, File2 gets data from File1. I used a formula in file2
=X:\Folder\[File1.xls]Totals'!$D$2. If both files are open on the same
computer, File2 updates automatically when data in File1 is changed. If I
open File2 on another computer I get a window that asks to "Update File". I
click OK and the file opens with the correct information, however it doesn't
update if data on File1 is changed after File2 is open.

Is there a way to have File2 update automatically when File1 is changed?

Thanks for your help.
Doug
 
D

Dave Peterson

File1 has to be saved so that File2 can get the info.

And you have to tell excel to go and get those values.

In xl2003 menus:
Edit|Links|select the link|Update values

If it's only one cell, you could just force a re-evaluation of that cell by:
Select the cell, Hit the F2 key, then hit the enter key.

=====
You can test this kind of stuff by opening two instances of excel and opening
each file in those separate instances.



I put this post under Gerneral Questions. I didn't get any good response so
I thought I would ask the experts.

I have 2 files, File2 gets data from File1. I used a formula in file2
=X:\Folder\[File1.xls]Totals'!$D$2. If both files are open on the same
computer, File2 updates automatically when data in File1 is changed. If I
open File2 on another computer I get a window that asks to "Update File". I
click OK and the file opens with the correct information, however it doesn't
update if data on File1 is changed after File2 is open.

Is there a way to have File2 update automatically when File1 is changed?

Thanks for your help.
Doug
 
D

Doug

I have File1 set up so it saves automatically after each entry. I was hoping
to have File2 update without the user doing anything. In other words, they
are just veiwing the screen. Can I write some code to update all the cells
File1 has to be saved so that File2 can get the info.

And you have to tell excel to go and get those values.

In xl2003 menus:
Edit|Links|select the link|Update values

If it's only one cell, you could just force a re-evaluation of that cell by:
Select the cell, Hit the F2 key, then hit the enter key.

=====
You can test this kind of stuff by opening two instances of excel and opening
each file in those separate instances.



I put this post under Gerneral Questions. I didn't get any good response so
I thought I would ask the experts.

I have 2 files, File2 gets data from File1. I used a formula in file2
=X:\Folder\[File1.xls]Totals'!$D$2. If both files are open on the same
computer, File2 updates automatically when data in File1 is changed. If I
open File2 on another computer I get a window that asks to "Update File". I
click OK and the file opens with the correct information, however it doesn't
update if data on File1 is changed after File2 is open.

Is there a way to have File2 update automatically when File1 is changed?

Thanks for your help.
Doug
 
D

Dan

Hi Doug,

You could look to use VBA to do this. Possible using using the worksheet
event procedures available to you:

Private Sub Worksheet_Change(ByVal Target As Range)

'FORCE A RECALCULATION ON WORKSHEET2

End Sub



Doug said:
I have File1 set up so it saves automatically after each entry. I was hoping
to have File2 update without the user doing anything. In other words, they
are just veiwing the screen. Can I write some code to update all the cells
File1 has to be saved so that File2 can get the info.

And you have to tell excel to go and get those values.

In xl2003 menus:
Edit|Links|select the link|Update values

If it's only one cell, you could just force a re-evaluation of that cell by:
Select the cell, Hit the F2 key, then hit the enter key.

=====
You can test this kind of stuff by opening two instances of excel and opening
each file in those separate instances.



I put this post under Gerneral Questions. I didn't get any good response so
I thought I would ask the experts.

I have 2 files, File2 gets data from File1. I used a formula in file2
=X:\Folder\[File1.xls]Totals'!$D$2. If both files are open on the same
computer, File2 updates automatically when data in File1 is changed. If I
open File2 on another computer I get a window that asks to "Update File". I
click OK and the file opens with the correct information, however it doesn't
update if data on File1 is changed after File2 is open.

Is there a way to have File2 update automatically when File1 is changed?

Thanks for your help.
Doug
 
D

Doug

Sorry, I'm a rank amature when it ccome to VB code. The second sheet is
actuall the first sheet in a separate file, it's not just another worksheet.
What code would I use to activate a recalculation in the second file?

Thanks again
Doug

Dan said:
Hi Doug,

You could look to use VBA to do this. Possible using using the worksheet
event procedures available to you:

Private Sub Worksheet_Change(ByVal Target As Range)

'FORCE A RECALCULATION ON WORKSHEET2

End Sub



Doug said:
I have File1 set up so it saves automatically after each entry. I was hoping
to have File2 update without the user doing anything. In other words, they
are just veiwing the screen. Can I write some code to update all the cells
File1 has to be saved so that File2 can get the info.

And you have to tell excel to go and get those values.

In xl2003 menus:
Edit|Links|select the link|Update values

If it's only one cell, you could just force a re-evaluation of that cell by:
Select the cell, Hit the F2 key, then hit the enter key.

=====
You can test this kind of stuff by opening two instances of excel and opening
each file in those separate instances.




Doug wrote:

I put this post under Gerneral Questions. I didn't get any good response so
I thought I would ask the experts.

I have 2 files, File2 gets data from File1. I used a formula in file2
=X:\Folder\[File1.xls]Totals'!$D$2. If both files are open on the same
computer, File2 updates automatically when data in File1 is changed. If I
open File2 on another computer I get a window that asks to "Update File". I
click OK and the file opens with the correct information, however it doesn't
update if data on File1 is changed after File2 is open.

Is there a way to have File2 update automatically when File1 is changed?

Thanks for your help.
Doug
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads


Top