concatenationreferenceerrorexcel-indirect

Indirect and Concatenate #ref error on *open* workbook

I've been trying to find the answer to this, and the only results I'm getting are #ref errors on closed workbooks. I'm getting this error when the workbook I am referencing is open.

So here's what I'm doing - I'm trying to blend numbers daily from different banks from multiple worksheets with multiple tabs each. Since those worksheets have a date followed by a random ID number, we're going to just rename them to a set name (eg, "pmac") and dump each into a folder. Then, we'd open each one we want to blend, and I'll use "ISERROR" to turn any reference errors to blank so only the open workbooks are included in the blend (which I'll try to add as soon as I can get this indirect reference to work).

The team that would be using this is on a shared drive, so I decided to make some dynamic fields "RatesheetFolder" and "PennyMacFileName", so if we ever needed to move or rename stuff, we'd change it in one place instead of updating every formula.

So currently, my formula looks like this:

=INDIRECT(CONCATENATE("='"&RatesheetFolder&"["&PennyMacFileName&"]Conventional'!B"&ROW(B17)))

but it returns a #REF error. Everything I'm reading is saying that it's because the workbook is closed... but it isn't. It's open. If I copy and paste the value from the formula above into a cell, then THAT reference works... so the concatenation wasn't done incorrectly either, but for some reason it won't reference the open workbook.

I also tried doing the same thing, but on a local directory, in case the shared drive was an issue... same problem though. (for reference, here is the pasted value of the above formula)

='C:\Users\username\Documents\Reports\In Progress\[pmac.xlsx]Conventional'!B17

I also tried leaving out the folder path entirely, since I'd be dealing with open workbooks, and that also didn't work (copy/pasting values of the concatenated string still works, so again, it's not that it's written incorrectly). Also tried having both files in the same folder, no luck.

Any idea what's going on? Is it some kind of security thing that my company might be blocking the indirect reference or something? Maybe the way I'm using the row function?

I am using Office 2016.

Additional:

So, boiling things down to their absolute minimum....

Thinking it was the equals sign causing the trouble, I tried this:

I tried the last two things again, but with the full directory path (not just filename) and same thing - actually writing the direct reference out worked

='C:\Users\username\Documents\Reports\In Progress\[pmac.xlsx]Conventional'!B17

but the indirect reference didn't

=INDIRECT('C:\Users\username\Documents\Reports\In Progress\[pmac.xlsx]Conventional'!B17

Oddly - when I did the Indirect formula with the full path and hit enter it removed the full path and changed it back to just the filename (since the workbook was open): =INDIRECT([pmac.xlsx]Conventional!B17)

but it still threw up a reference error. So it recognizes the workbook and that it's open... but it still gives an error trying to reference it. The indirect-concatenate does seem to work when I'm not referencing another workbook, so it does seem that's where the issue is... but I don't know why.

Solution

  • So I guess I found the answer after messing around with quotation marks and stuff in both the indirect formula and the concatenate formula.

    To start, I realized that INDIRECT(B2) gave a ref error, while INDIRECT("B2") worked. So I went back through and tested a few things out making sure concatenate returned the exact right thing, and then surrounded that with an indirect formula.

    So when I did =INDIRECT([pmac.xlsx]Conventional!B17) this didn't work because it wasn't a string. If I did =INDIRECT("[pmac.xlsx]Conventional!B17"), THEN it worked. Indirect requires it to be a string.

    Looks like the problem was that Concatenate returned [pmac.xlsx]Conventional!B17 as text - but when I copy/pasted the VALUE that the concatenate formula returned, that then reverted the string back to a value, and stopped working inside the indirect formula.

    What ended up working (and I swear I tried this already....) was:

    =INDIRECT(CONCATENATE("'"&RatesheetFolder&"["&PennyMacFileName&"]Conventional'!B"&ROW($A17)))

    I must have accidentally removed the single quote mark before RatesheetFolder when I removed the equal sign causing it to fail, and then every attempt after that was me trying to break it down into the individual parts, and unknowingly undoing the "string" property that concatenate applied whenever I pasted the result it into the indirect formula.

    TL;DR of what I figured out

    1. The Indirect formula requires it's contents to all be text/string
    2. The results of Concatenate are returned as text/string, making additional quotation marks around those results redundant and cause an error
    3. If you're referencing a cell directly, you need the equal sign =B2, but if you're doing an indirect reference, you want to leave the equals sign out of the indirect formula =INDIRECT("B2")

    So putting that all together (in this example DynamicVar's value is simply the letter B) :