How do I modify my Count IF formula to return BLANK when referenced worksheet is missing?

Ok, This formula did the job nicely (see attached).
[=COUNTIF(INDIRECT("'"&E$4&"'!$D$5:$AC$100"),$B6)

I now want to modify this formula to return a BLANK (or "") when referenced wksht is missing, instead of #REF!.

Any suggestions?

Gary
LCS-by-Activity-Code-08-2011-EE-.xls
garyrobbinsAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
reitzenConnect With a Mentor Commented:
Wrap it in another IF statement

=IF(ISERROR(your formula here),"",your formula here)
0
 
barry houdiniConnect With a Mentor Commented:
To avoid repeating the whole formula you can just check whether referring to A1 on that sheet using INDIRECT gives an error, e.g.

=IF(ISERR(INDIRECT("'"&E$4&"'!A1")),"",COUNTIF(INDIRECT("'"&E$4&"'!D5:AC100"),$B6))

regards, barry
0
 
garyrobbinsAuthor Commented:
reitzen: Awesome.  I have not yet used the ISERROR function and I now see how simple it is.

Barry, thanks for describing yet another way of addressing the issue -- that may be good for a future application where my formula is long and complicated.

I've split the points - hope you find this fair.

Thanks, again for the prompt response.  You make my job easier knowing you experts are there to help.
Gary
0
All Courses

From novice to tech pro — start learning today.