When I close linked spreadsheet I get #REF! Error

[kjuran]

Hello,

So, here is my issue. I have to spreadsheets linked by (1) using a concatenate/match function (i.e. CONCATENATE("k",MATCH(I38,'K:\Kayla \[Copy of Data - Exp 1.xlsx]Data (2021)'!$C$4:$C$787,FALSE)+3) and (2) using the indirect function to display the data from the cell given from the previously stated function. However, when I close out of the document that my data is sourced from, the cells that use the indirect function in the destination sheet start showing the error #REF! . Is there a workaround for this so that the sheet can function even when the source sheet is closed?

Any help would be greatly appreciated!

 

[6StringJazzer]

INDIRECT references to an external file require the external file to be open, or it returns REF#. The workarounds all require VBA. There are multiple approaches:

1. Use INDIRECT.EXT which is an add-in available in various places. It is used just like INDIRECT but under the covers it open the file. It is not commercial so I'm not sure where it is currently available. I may have a copy.
2. Write code that creates your formula when the data changes. Then the file will not have to be open at all.

I can help with this but would need all the details about your formulas. The ideal would be to share your file on a file-sharing service.

 

[kjuran]

Here is essentially what I have. What do you mean by option 2? Could you be more specific?

 

[kjuran]

INDIRECT references to an external file require the external file to be open, or it returns REF#. The workarounds all require VBA. There are multiple approaches:

1. Use INDIRECT.EXT which is an add-in available in various places. It is used just like INDIRECT but under the covers it open the file. It is not commercial so I'm not sure where it is currently available. I may have a copy.
2. Write code that creates your formula when the data changes. Then the file will not have to be open at all.

I can help with this but would need all the details about your formulas. The ideal would be to share your file on a file-sharing service.

Nevermind, I just used the VLOOKUP function and it worked!

 

[6StringJazzer]

I see no way that what you described can be solved by VLOOKUP. Can you share the formula that ended up working for you?