Excel - Link Refresh option ? 2

[DaleWatson123321]

To my knowledge, in order for Excel to update formulas which reference other files, those other files MUST be loaded into memory. They can be opened "manually", or they can be saved in a "Workspace" file which opens ALL the files associated with the Workspace file.

My question: Is this the ONLY option - i.e. does one HAVE to load ALL files into memory ? Is it possible to update "link formulas" WITHOUT having to load the files into memory ?

The background to this is that in Lotus 123, there is a "Link Refresh" option which updates linked formulas EXTREMELY FAST, and these other files do NOT have to be loaded into memory. They can be in various folders on the local drive and/or on network drives.

Does anyone know of a "hidden feature" in Excel which would perform the equivalent "Link Refresh" function as in Lotus 123 - i.e. WITHOUT having to load the files into memory ?

I continue to find "hidden features" in Excel, so I hope others might have discovered one which relates to this situation.

MANY thanks if you can provide a solution to this ! ! !

Regards, ...Dale Watson dwatson@bsi.gov.mb.ca

 

[Dreamboat]

Dale: When you say "loaded into memory", do you mean "open"? If so, I don't find that to be true at all. Under Tools-Options, Calculation, I have these two items checked in Excel 97:

Update remote references
Save external link values

If that's not the case, could you explain what "loaded into memory" means?

http://www.VictimPhotos.com

http://www.PiersonTech.com

Brainbench MVP for Microsoft Word
techsupportgirl@home.com

 

[DaleWatson123321]

Thanks, Dreamboat, for your response.

I too am using Excel 97 SR2.

And I have had checked off the same items you refer to:
...Update remote references, and
...Save external link values

However, the formula ...
=INDIRECT("["&filename&"]"&"DaveAccounts!test&quot
...does NOT update without the file being loaded into memory.

As a result of your response, however, you made me "an instant believer".

Indeed, after creating a "normal" formula - i.e. WITHOUT using the "INDIRECT" function, it did work - i.e. without the file being loaded into memory.

Now, I'll have to "re-work" the formula I had created, to get around the fact that the "INDIRECT" function does NOT work in this situation.

Thanks again.

Regards, ...Dale Watson dwatson@bsi.gov.mb.ca

 

[Dreamboat]

Ubet. Something else comes to mind, too. I don't know if you've heard it before--I've only heard this recently, and may even have heard it from YOU, LOL. If you have one spreadsheet open that is set to Manual Calculation, no other worksheets opened at the same time will auto-calc either. Have you heard that? I didn't test it.

http://www.VictimPhotos.com

http://www.PiersonTech.com

Brainbench MVP for Microsoft Word
techsupportgirl@home.com

 

[JVFriederick]

The manual recalc setting DOES override all other spreadsheet files during that session of Excel.

Dale, please send me that file on how to print various ranges on one page you mentioned recently. JVFriederick@Yahoo.com
I'm curious what this "feature" does to the file size.
Thanks

 

[DaleWatson123321]

Dreamboat,

Thanks again for your info on the potential of having a file open which has its calculation mode set to "Manual".

That wasn't the case in my situation. The problem simply centered around the "INDIRECT" function not working in this situation - i.e. where it involves a "linked formula".

As an alternative, I created the following VBA routines for the (Tek Tips) user. This relates to thread68-143414 from “Hasit” where his question was “Can I pass a string in a cell to construct a filename...


Dim filename, pathname, rangename As String
Dim filetoget, pathtoget, rangetoget, pathfile As String

'These routines enter either a formula or a value into the
'current cell, based on the following variables which'
'are currently set up as range-named cells within the
'file, but could be set up as variables within the VBA
'code, depending on which is preferable.


Sub Create_Formula()
'enters a “link formula” into the current cell

filetoget = Worksheets("Sheet1&quot.Range("filename&quot
pathtoget = Worksheets("Sheet1&quot.Range("pathname&quot
rangetoget = Worksheets("Sheet1&quot.Range("name_in_file&quot

pathfile = "='" & pathtoget & filetoget & "'!" & rangetoget

ActiveCell.Formula = pathfile

End Sub


Sub Enter_Value()
'enters a “value” into the current cell

filetoget = Worksheets("Sheet1&quot.Range("filename&quot
pathtoget = Worksheets("Sheet1&quot.Range("pathname&quot
rangetoget = Worksheets("Sheet1&quot.Range("name_in_file&quot

pathfile = "='" & pathtoget & filetoget & "'!" & rangetoget

ActiveCell.Value = pathfile

Selection.Copy
Selection.PasteSpecial Paste:=xlValues
Application.CutCopyMode = False

End Sub

==========================

To JV.

The file you asked for is one its way.

==========================


Regards, ...Dale Watson dwatson@bsi.gov.mb.ca

 

[JVFriederick]

Got it.
Now I gotta find that original thread and give another purple star !!

 

[Hasit]

I have just logged on and picked up this thread. Some excellent stuff here and a personal "Thanks" to Dale for putting in a lot of work to get me a solution to my problem.