Limitation of LET and external workbook links?

[dreid1011]

I have the following formula:

=LET(z,"G:\ ... \Daily Detail Worksheets\[Z Checks- ... .xlsm]",IF(AND('[z]Daily Input'!$F$1=$F$3,'[z]Daily Input'!$E$7>0),'[z]Daily Input'!$E$7,0))

and the workbook is unable to refresh the links after I began using the LET formula. Without using LET, the links are just fine. Is this a limitation of LET because outside the formula Excel doesn't know what "z" is?

 

[Joe4]

If you are trying to build a dynamic link to an external workbook, I believe you need to incorporate the INDIRECT function, as shown here:

Dynamic workbook reference

To build a dynamic worksheet reference – a reference to another workbook that is created with a formula based on information that may change – you can use a formula based on the INDIRECT function. In the example shown, the formula in E6 is: =INDIRECT("'["&B6&"]"&C6&"'!"&D6) Note: the external...

exceljet.net

 

[dreid1011]

If you are trying to build a dynamic link to an external workbook, I believe you need to incorporate the INDIRECT function, as shown here:

Dynamic workbook reference

To build a dynamic worksheet reference – a reference to another workbook that is created with a formula based on information that may change – you can use a formula based on the INDIRECT function. In the example shown, the formula in E6 is: =INDIRECT("'["&B6&"]"&C6&"'!"&D6) Note: the external...

exceljet.net

I thought about using that, but ended up not, and it works once I establish the link, but after closing/reopening, I have to manually establish the links each time.

Before, I was just using the formula without the LET and the workbook names were hard coded into the formulas and the links were updating just fine.

I will give this a try.

 

[Joe4]

I don't think you are understanding the purpose of INDIRECT and when to use it.

Hard-coded range or file references work fine. However, if you are trying to build a range or file references dynamically by formulas, you MUST use INDIRECT!
Otherwise, Excel will treat what you are building as a literal string, and not an object reference.

You can still use you LET formula to shorten it, but you still need to use the INDIRECT function inside it where you are building the actual file reference by formula.

 

[dreid1011]

The resource that I found doing exactly what I have currently conveniently left out the INDIRECT then, but at the time it made sense to me because the workbook/sheetname is essentially just a string. But I suppose there is some difference in how it is handled as a string vs an object reference. And maybe I have misunderstood INDIRECT, I just know it as a means to make a dynamic string.

 

[dreid1011]

Perhaps you can suggest how I should use INDIRECT in this instance, because I am trying and just end up with #REF! errors.

 

[Joe4]

The article I referenced explains the difference (please read it closely) - if the whole workbook path and file reference is hard-coded into a single string, you do not need INDIRECT.
However, if you are building the string by any sort of formula, you do!

And you have not written that formula correctly anyhow - the literal parts need to be placed in between quotes, while the variable is sewn in with &, i.e.
where you are retying to use your "z" variable, instead of this:

...AND('[z]Daily Input'!$F$1...

it needs to look something like this:

...AND("'[" & z & "]Daily Input'!$F$1"...

Also note that this other workbook MUST be open on your computer. It cannot reference closed workbooks in this manner.

 

[Joe4]

Here is a little trick you can use to create these dynamic references. First create an example of what you are trying to do by doing the following:

Open up your other workbook, so both are open on your computer.
Then, type equal into any blank cell on your original workbook, and then go to your other workbook, select the cell you want, and click enter.
Now, inspect the formula it just placed in your cell (that is the hard-coded direct reference).
In my example, it looks like this for me:

=[Book12.xlsm]Sheet2!$A$4

Now, let's say that I don't want the file name to be hard-coded, because it could change. Instead, it will be a value, let's say in cell C2. So I want to use the value in cell C2 as the workbookname.
The first thing to try to do is to write a formula that creates a string that looks like the cell reference formula shown above.
I know it needs to start with a literal "[", so I hard-code that. Then, I add in my value from range C2 (so just a range reference call), and then the rest is hard-coded.
So I write it like this:

="[" & C2 & "]Sheet2!$A$4"

I then look at the value that string returns. Does it look exactly like the hard-coded reference I had in the first step?
If not, make the adjustments so that it does.
Once you have that looking exactly the way it should, then simply surround that formula with INDIRECT to tell Excel that you do not want it to return the literal string we built, but rather to treat that as a file/range reference, and return that value, i.e.:

=INDIRECT("[" & C2 & "]Sheet2!$A$4")

 

[dreid1011]

The article I referenced explains the difference (please read it closely) - if the whole workbook path and file reference is hard-coded into a single string, you do not need INDIRECT.
However, if you are building the string by any sort of formula, you do!

And you have not written that formula correctly anyhow - the literal parts need to be placed in between quotes, while the variable is sewn in with &, i.e.
where you are retying to use your "z" variable, instead of this:

...AND('[z]Daily Input'!$F$1...

it needs to look something like this:

...AND("'[" & z & "]Daily Input'!$F$1"...

Also note that this other workbook MUST be open on your computer. It cannot reference closed workbooks in this manner.

It's been a couple of days and I don't recall exactly, but I feel like '[z]Daily Input' was established by Excel after it opened a directory for me to manually choose the linked worksheet. But I could very easily be mistaken. Anyway, if the other workbook has to be open, then I'll just go back to the original formula with hardcoded file paths so I don't have to have the others open at the same time.

Thank you for the information.

 

[Joe4]

By the way, assuming that your original references in your original post are correct, I think this is how you would need to rewrite your original formula using LET:

=LET(z,"G:\ ... \Daily Detail Worksheets\[Z Checks- ... .xlsm]",IF(AND(INDIRECT("'[" & z & "]Daily Input'!$F$1")=$F$3,INDIRECT("'[" & z & "]Daily Input'!$E$7")>0),INDIRECT("'[" & z & "]Daily Input'!$E$7"),0))

(of course, assuming the other file is open).