title: Indirect and Concatenate #ref error on *open* workbook
body: <p>I've been trying to find the answer to this, and the only results I'm getting are #ref errors on <em>closed</em> workbooks. I'm getting this error when the workbook I am referencing is open.</p>
  
  <p>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).</p>
  
  <p>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.</p>
  
  <p>So currently, my formula looks like this:</p>
  
  <pre><code>=INDIRECT(CONCATENATE("='"&amp;RatesheetFolder&amp;"["&amp;PennyMacFileName&amp;"]Conventional'!B"&amp;ROW(B17)))
  </code></pre>
  
  <p>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.</p>
  
  <p>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)</p>
  
  <pre><code>='C:\Users\username\Documents\Reports\In Progress\[pmac.xlsx]Conventional'!B17
  </code></pre>
  
  <p>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.</p>
  
  <p>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?</p>
  
  <p>I am using Office 2016.</p>
  
  <p><strong>Additional:</strong></p>
  
  <p>So, boiling things down to their absolute minimum....</p>
  
  <ul>
  <li><code>=$B$2</code> returns the value in B2</li>
  <li><code>=CONCATENATE("="&amp;ADDRESS(2,2))</code> returns the text "=$B$2"</li>
  <li><code>=INDIRECT("=$B$2")</code> returns a #ref error</li>
  <li><code>=INDIRECT("$B$2")</code> returns the value in B2 (so only without the "=")</li>
  <li><code>=INDIRECT(CONCATENATE("="&amp;ADDRESS(2,2)))</code> returns a ref error</li>
  <li><code>=INDIRECT(CONCATENATE(ADDRESS(2,2)))</code> returns the value in B2</li>
  </ul>
  
  <p>Thinking it was the equals sign causing the trouble, I tried this:</p>
  
  <ul>
  <li><code>=[pmac.xlsx]Conventional!B17</code> which returned the value from the other workbook</li>
  <li><code>=INDIRECT([pmac.xlsx]Conventional!B17)</code> still returns a ref error...
  so I'm lost again.</li>
  </ul>
  
  <p>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</p>
  
  <p><code>='C:\Users\username\Documents\Reports\In Progress\[pmac.xlsx]Conventional'!B17</code></p>
  
  <p>but the indirect reference didn't </p>
  
  <p><code>=INDIRECT('C:\Users\username\Documents\Reports\In Progress\[pmac.xlsx]Conventional'!B17</code></p>
  
  <p>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):
  <code>=INDIRECT([pmac.xlsx]Conventional!B17)</code></p>
  
  <p>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.</p>