Solved

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

Posted on 2011-09-07
3
240 Views
Last Modified: 2012-05-12
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
0
Comment
Question by:garyrobbins
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
3 Comments
 
LVL 6

Accepted Solution

by:
reitzen earned 450 total points
ID: 36498797
Wrap it in another IF statement

=IF(ISERROR(your formula here),"",your formula here)
0
 
LVL 50

Assisted Solution

by:barry houdini
barry houdini earned 50 total points
ID: 36498875
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
 

Author Closing Comment

by:garyrobbins
ID: 36499046
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

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

710 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question