How do you find the formula reference in Excel?

January 25, 2017 at 13:26:09
Specs: Windows 10 Pro
I have tried:

Data, Edit Links
Formula, Name Manager

Neither shows the reference to the Temp directory yet i get an error message saying "Sorry, we couldn't open "remote Temp file on an unknown server"

See More: How do you find the formula reference in Excel?

Report •

January 25, 2017 at 16:41:41
Did you try searching for "temp" using the Find feature? Make sure the option is set to "Look In Formulas".

Have you tried setting the option to show formulas and then 'physically' looking for formulas that reference external workbooks? That could be tedious, but...

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Report •

January 26, 2017 at 07:02:12
Thanks, I have tried both of those approaches. FYI, I've had this problem for 6 months and the reference I'm looking for is outside the company and apparently imbedded by someone who previously worked for the the organization named in the temp file reference. It does not seem to be in any workbook or formula but rather is in the file structure.

Report •

January 26, 2017 at 08:04:31
I'm not sure how an reference could be in the "file structure", but that could be beyond my expertise.

Maybe something at the following links will help. The idea of using a macro to find the offending reference popped into my head so I did a quick Google search.

This one looks interesting:

I can't open the following link (corporate computer restrictions) so I can't say if it would be of any help. The title in Google is: "How to find and list all links (external references) in Excel?"

Good luck!

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Report •

Related Solutions

January 26, 2017 at 09:49:50
Thanks again.
I found the link and followed all the tips to no avail. I did not try the macros because it looks too complicated and still looks in fomulas within cells. The focus seems to be finding links within formulas and that is not my problem. I have checked the formulas multiple times, broken links to replace formulas with values, eliminated names from Name Manager and when I save and close the file I get the error message when I reopen. I'm tediously trying to rebuild the file and avoid copying sheets into my new file. Instead, I copy values, then go back and copy a formula and replicated it in my new files. At some point I'm introducing the reference to the "foreign server" which only shows up when I say "Update Links"; it always results in the error message.

Report •

January 26, 2017 at 10:46:36
Shot in the dark here.
Not sure if it is even related to your problems.

Have you checked all of your Options in the workbook?

The only two that might be of interest are:

Under the Advanced section

When Calculating this workbook:

The two boxes:

Update Links to other Documents
Save external link values

Like I said, a shot in the dark.


Report •

January 27, 2017 at 14:40:08
Thanks, Mike.
I tried your suggestion and it did not work. Even if it didn't work, I've gotten more help from this site than anything I've ever tried. Thanks!

Report •

January 27, 2017 at 17:54:04
Are you on a network?

The error message:
"Sorry, we couldn't open "remote Temp file on an unknown server"

Would seem to point to some type of mis-configuration.

I do know that excel does open a Temp directory, usually located at:

"C:\ Documents and Settings\<username>\Local Settings\Temp"

and when a new file is opened a temporary file is created in the Temp folder.

I believe that if the file is stored on a network drive then the folder & file
are created there.

Might want to poke around and see if you have a Temp folder and where exactly
it is located.


Report •

January 28, 2017 at 07:39:00
The problem is not on my computer; I'm not on the network but people I work with are. One of them thinks Outlook is a filing system and opens and saves Excel files in his email. I got file from him that contaminates my files if I use his info (sales forecast). There is a reference in there to a "foreign server" that does not show up in links or anything I've been able to check. It is imbedded somewhere by Excel and does not show up until you "Update Links". When I bypass the error and update the file for him, his computer freezes up. I really appreciate your help on this!

Report •

January 28, 2017 at 08:23:34
Sorry, but my knowledge of networks is very limited, but one thing you might check
is to make sure you and your colleague are both on the same servers with the same

Is it possible he has a server connection with a different name?
I know that some IT departments are fond of changing names.

Only other thing that come to mind is to check your Trust Settings.
Under Options there should be a Trust Center button and
within the trust center window there should be another button: Trust Center Settings.
You might check and see if everything looks OK, look in the Trusted Locations section and especially the External Content section.

Sorry can't be of more help.


Report •

Ask Question