Software

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.

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

Related Discussions

Related Forums