Excel formula
The IF() is the wrong one to use. Use "Iif()", as in:
=IiF(A3='Audit Exception Register'!B:B, COUNTIF('Audit Exception Register'!P:P,Y), 0)
See:
http://pubs.logicalexpressions.com/Pub0009/LPMArticle.asp?ID=799
Caveat about using Iif(), one that doesn't probably apply in this case, but keep it in mind: Don't use Iif when trying to return the values of "real" objects. Examples of "non-real" objects in the VB/A world are:
String
Integer, Double, etc... all the numeric "objects"
Boolean
These are fine to use in IIf() statements. If however you will be returning some value that is part of an object, as in:
Iif(x=2, myObject.getSomeVal(), 0)
You run the very real risk that is myObject is null, your program will simply stop running. Iif() will NOT return a null error/exception; it will in fact just stop. This is a bug in the VB interpreter and/or in the Iif() function itself.
I am referring here to classic VBA (ie, pre-VB dot-Net). How .NET handles such cases, I don't know.
×