Hiding !REF# Error using =indirect() function

**Ace of Clubs** · 07-07-2009, 01:54 AM

I am setting up a summary sheet that contains =indirect() functions for workbooks that don't exist yet.

I would like some kind of function that returns the =indirect() function correctly if the workbook exists and just a 0 or blank if the workbook doesn't exist.

I would like to have the indirect function in all of cells that as soon as someone creates a workbook it will update the summary sheet.

Does anybody have a workaround for this issue.

Thanks in advance

**JBeaucaire** · 07-07-2009, 02:02 AM

Wrap your existing INDIRECT() formulas in an IFERROR...

=INDIRECT(A1&"!B25")

...becomes:
=IFERROR(INDIRECT(A1&"!B25"), "")

**Ace of Clubs** · 07-07-2009, 02:09 AM

Originally Posted by JBeaucaire

Wrap your existing INDIRECT() formulas in an IFERROR...

=INDIRECT(A1&"!B25")

...becomes:
=IFERROR(INDIRECT(A1&"!B25"), "")

Thanks again.

You are a pro.

**DonkeyOte** · 07-07-2009, 02:10 AM

Be aware that INDIRECT will only work with external sources if they are open.

If the external files exist but are closed you will also generate a #REF error - you may want to investigate INDIRECT.EXT available within the morefunc add-in:

info: http://xcell05.free.fr/morefunc/english/
download: http://download.cnet.com/Morefunc/30...-10423159.html

**Ace of Clubs** · 07-07-2009, 02:20 AM

Originally Posted by DonkeyOte

Be aware that INDIRECT will only work with external sources if they are open.

If the external files exist but are closed you will also generate a #REF error - you may want to investigate INDIRECT.EXT available within the morefunc add-in:

info: http://xcell05.free.fr/morefunc/english/
download: http://download.cnet.com/Morefunc/30...-10423159.html

Thank you.