1

I'm using excel 2016 and I've created an INDIRECT formula to combine different cell values into a link but I'm getting #REF!

The cell I'm trying to link to is in another workbook on a network drive and I'm using the UNC folder name. I've compared it to a normal link to the same cell and evaluated my formula and they look identical.

The non-dynamic link (which works!), is this....

='\\admin\user group\aob\[excel file.xlsx]Summary'!A1

The dynamic link (which doens't work) looks like this....

=INDIRECT("'"&$B$8&$B$12&"'!"&Cell_ref!A1)

where $B$8 (the file) is: \admin\user group\aob[excel file.xlsx] and $B$12 is: Summary and Cell_ref!A1 is: $A$1

The value returned is #REF! but it should be the value from the cell being referenced.

Any help gratefully received!

Accidental Excelist
  • 11
  • 1
  • If the reference is invalid, or if the workbook referenced is not open, `INDIRECT()` will throw a #REF error – JvdV Apr 12 '19 at 09:17
  • @JvdV - I didn't realise the referenced workbook has to be **open**? That sounds like a major limitation of the INDIRECT function (it obviously doesn't need ot be open in a non-dynamic link).Any idea what techniques people use to work around this? – Accidental Excelist Apr 12 '19 at 09:30
  • If you explain what it is you are trying to accomplish, perhaps we can give suggestions? – jkpieterse Apr 12 '19 at 09:32
  • Yes, have a look over [here](https://stackoverflow.com/questions/28461672/referencing-value-in-a-closed-excel-workbook-using-indirect) – JvdV Apr 12 '19 at 09:33
  • Thanks @jkpieterse - I'm creating whole worksheets which link to worksheets in another 'source' workbook (and then adding below the linked area). I was hoping to have a dynamic link for the filename so if a later version of the source file is created (with a different name), it would be easy to update all the links. I suppose I could just use 'Find/Replace' but I was hoping INDIRECT would give a sleeker solution.... – Accidental Excelist Apr 12 '19 at 10:57
  • 1
    Thanks for your help @JvdV – Accidental Excelist Apr 12 '19 at 10:58
  • Data, Edit links, Change Source is the simplest way I'd say. – jkpieterse Apr 12 '19 at 17:13

0 Answers0