Excel external workbook link issue

[Dheeraj]

Hi all,

Yesterday one of my team member showed me an error he was getting in a report. This is very strange kind of error which I never heard from anyone nor faced it. Also, unable to replicate it. However, seen it on my colleage's system. We tried to fix it but no success

1. there are 4 workbooks (a.xlsx, b.xlsx, c.xlsx and d.xlsx)
2. one of those workbooks (a.xlsx) has links from other workbooks i.e. it consolidates data from them.
3. A sample example can be taken as explained below:
(a). workbook [b.xlsx]sheet1!A1=1
(b). workbook [c.xlsx]sheet1!A1=2
(c). workbook [d.xlsx]sheet1!A1=3
(d). workbook [a.xlsx]sheet1!A1= workbook [b.xlsx]sheet1!A1 + workbook [c.xlsx]sheet1!A1 + workbook [d.xlsx]sheet1!A1

So output in workbook [a.xlsx]sheet1!A1 should be 6 and it is coming. There is no error till this point.

Now below is the issue:

1. workbook with final output is open and rest other workbooks are closed. So in this case only a.xlsx is opened,
2. Now if we manually open any of the closed workbook (b.xlsx, c.xlsx and d.xlsx), formula typed in workbook named a.xlsx shows #REF error.

Please help me understand what is causing this error.

I hope no need to upload sample workbooks coz I too unable to replicate it.

Thanks,
Dheeraj

 

[nazmul_muneer]

Dear Dheeraj,
You can check/followings points
1. You can try to keep all the 4 Excel Workbooks in a folder
2. Avoid to link from book1, book2, book3...... because when you open a new workbook it is named book1 or book2 or book3.... So the summary file will call data form this opened temporary workbooks if it matches the workbook name.
3. Check whether any workbook has circular reference
4. Check Cell A1 of any workbook has error/text value
5. If source workbook name is matched, summary workbook will call data from the opened workbook, so it may represent wrong value
6. The best way to check the formula debugging F9 with single part
7. Check the formula using
workbook [a.xlsx]sheet1!A1= workbook [b.xlsx]sheet1!A1
workbook [a.xlsx]sheet1!A2= workbook [c.xlsx]sheet1!A1
workbook [a.xlsx]sheet1!A3=workbook [d.xlsx]sheet1!A1
Which cell represent ERROR!

 

[SirJB7]

Hi, Dheeraj!
Repeated your workbook structure and if works fine both updating and not updating links at open time (consolidated workbook). My suggestion is to try the process in other standalone machines (not connected to a network, if it'd be the case) and try to reproduce the issue.
Otherwise consider uploading a set of sample files.
Regards!

 

[kchiba]

This may be a problem of not finding the Workbook, remember the formula "Changes" for closed and open workbooks.

If you link to an open workbook, there is no path:

This is the formula when the source file is closed:

='C:\Users\xxx\Desktop\[Sum2011.xlsb]Bank_DB'!$F$1519

this is the formula when the source file is opened

=[Sum2011.xlsb]Bank_DB!$F$1519

So when your source book is closed do you have the correct path name in the formula?

regards

kanti

 

[Dheeraj]

Hi All,

Thanks for your support

I have already checked with the points mentioned in replies. However, still unable to solve it.

Unfortunately unable to upload any sample workbook.

Anyways, I have found few links on google and these have some concepts. Let me try them and if those works, I'll share them with all.

Thanks,
Dheeraj

 

[SirJB7]

My suggestion is to try the process in other standalone machines (not connected to a network, if it'd be the case) and try to reproduce the issue.

Hi, Dheeraj!
Have you tried that? With what result? Could you describe in detail the messages and any other useful information that you got displayed?
Regards!