Connect Files

[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

 

[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

 

[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

 

[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

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

 

[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

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

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