General discussion

Locked

Excel INDIRECT() function limitations

By adh ·
Use of the INDIRECT(ref_text,a1) function in Excel insists that "if ref_text refers to another workbook (an external reference), the other workbook must be open. If the source workbook is not open, INDIRECT returns the #REF! error value."

Is there another function I can use that uses cell text as an external reference to another workbook that does not require that workbook to be open?

This conversation is currently closed to new comments.

6 total posts (Page 1 of 1)  
| Thread display: Collapse - | Expand +

All Comments

Collapse -

Excel INDIRECT() function limitations

by motek In reply to Excel INDIRECT() function ...

=VLOOKUP(G19,'\\SERVER\VOL1\PROJECTS\FORMS\[TOOLFILE.XLS]TOOLFILE'!$A$2:$D$6000,2,FALSE)
Try using the Vlookup, This doesn't require the other file to be open. Above is a copy of the formula that I use. Hope this helps

Collapse -

Excel INDIRECT() function limitations

by adh In reply to Excel INDIRECT() function ...

This is no good to me unfortunately because you are using a specific filename "\\SERVER\ ... [TOOLFILE.XLS].

I need to use the contents of another cell as the filename, and the usual function for doing this is the INDIRECT function - sadly this doesn't work if the external reference file isn't open.

I need a solution that allows the external file reference to be a variable name stored in another cell.

Thanks for trying!

Collapse -

Excel INDIRECT() function limitations

by adh In reply to Excel INDIRECT() function ...

This is no good to me unfortunately because you are using a specific filename "\\SERVER\ ... [TOOLFILE.XLS].

I need to use the contents of another cell as the filename, and the usual function for doing this is the INDIRECT function - sadly this doesn't work if the external reference file isn't open.

I need a solution that allows the external file reference to be a variable name stored in another cell.

Thanks for trying!

Collapse -

Excel INDIRECT() function limitations

by jonbg In reply to Excel INDIRECT() function ...

You don't mention what you are using the INDIRECT function for. An easy way to get ref_text from a closed workbook is with a simple reference formula. Have INDIRECT refer to that formula and you've got your reference! Whether this solves your problem depends on where the reference points to. Did I understand you correctly?

Collapse -

Excel INDIRECT() function limitations

by adh In reply to Excel INDIRECT() function ...

I've tried formulas in the INDIRECT function, and it still insists the file is open.

Collapse -

Excel INDIRECT() function limitations

by adh In reply to Excel INDIRECT() function ...

This question was closed by the author

Back to Software Forum
6 total posts (Page 1 of 1)  

Related Discussions

Related Forums