Quick Question about References to Closed Workbooks

largeselection

Active Member
Joined
Aug 4, 2008
Messages
358
Hi,

So I'm encountering a weird issue so I'm wondering if someone can shed some light on it for me.

In a workbook, I can write a formula to reference a cell in a closed workbook like this:

='E:\Desktop\[Performance.xlsx]Data'!$B$158

The thing that is weird is that it only works some of the time...

So I get a #REF error, but if I open the workbook that it is referring to then I get the result of the formula. The weird thing is that I have 20 different workbooks which I reference and that formula works exactly as expected for 17 of them when they are closed. There are 3 that for some reason the workbook has to be open for the formula to work.

I would have thought it would be all or none. So either all of those formulas which reference all 20 worksbooks would #REF errors or none of them would be.

Any ideas why this would be happening?

I've checked the formula a bunch of times so I don't think it's incorrectly written (especially since it works correctly when the file is open).

Thanks for any ideas.
 

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.
Are you sure that the ones giving the #REF errors have been saved as Excel workbooks? ( I've seen people save csv into a .xls extension )
 
Upvote 0
Hi,

Thanks for looking! Yes they are all excel workbooks. They are all generated by the same system so they are very uniform.

Thanks!
 
Upvote 0
Are all the formulas that give #REF errors just direct links? Or are they embedded within Excel worksheet functions?
 
Upvote 0
Hi,

They are all just direct links and not enbedded in other functions. They all have the same format as:

='E:\Desktop\[Performance.xlsx]Data'!$B$158

Just instead of Performance.xlsx, it is Performance2.xlsx
 
Upvote 0
Now I'm noticing another weird thing...

For some reason my code is skipping row 26. There's no reason that I can see that would cause it to do that.

Basically the code runs through all the files in a folder and adds these direct link formulas to a sheet to pull data from all the files in the folder. It adds only to 1 row and then skips down to the next row. For some reason it does just the first part of Row 26 (puts the formula in cell A26) and then skips the rest of them and goes right to Row 27. There is no error handler that would cause that either.

Something funky is going on it seems
 
Upvote 0
More bizarre stuff. So I did an experiment where I ran it and then I didn't value out the formulas. So I went into one of the cells where I got the #REF error. I opened the file that corresponded and the #REF error evaluated correctly.

The weird part is that when I then closed the file and re-entered the cell and re-calculated, the cell STILL evaluated correctly and had the correct path and everything for the link. I compared that formula to the same formula that was originally in the cell (which threw the REF error) and they were 100% the same.

So why would opening and closing the reference file allow the formula to be calculated correctly?

It's not like with Indirect because for that the file always has to be open for it to work.

So why would this formula not need the file to be open, but sometimes need the file to be opened just for a minute?
 
Upvote 0
I ended up tweaking the code to use RDB's ADO connection method and that circumvented all the issues with the formulas.
 
Upvote 0

Forum statistics

Threads
1,213,535
Messages
6,114,192
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top