Validate input with check mark

Validate input with check mark

Generic formula 

=IF(logical_test,"P","")

Explanation

To display a check mark if the value passes some kind of test, you can use a formula based on the IF function together a symbolic font like Wingdings.

In the example show, the formula in C5 is:

=IF(COUNTIF(allowed,B5),"P","")

Where the cells in column C are formatted with Wingdings 2.

How this formula works

At the core, this is just an IF statement set up to return a capital P when TRUE:

=IF(logical_test,"P","")

If the test returns FALSE, the formula returns an empty string (""). To display "P" as a check mark, cells in column C are formatted with Wingdings 2.

For the test, we are using the COUNTIF function like this:

COUNTIF(allowed,B5)

COUNTIF checks if the value in B5 occurs in the named range "allowed". If the value is found, COUNTIF will return a positive number, if not, COUNTIF will return zero. This works perfectly as the logical test for IF.

With fixed values

The example above shows allowed values in a range of cells, but allowed values can also be hard-coded into the formulas as an array constant like this:

=IF(COUNTIF({"red","blue","green"},B5),"P","")

Validate input with check mark

Generic formula 

=IF(logical_test,"P","")

Explanation

To display a check mark if the value passes some kind of test, you can use a formula based on the IF function together a symbolic font like Wingdings.

In the example show, the formula in C5 is:

=IF(COUNTIF(allowed,B5),"P","")

Where the cells in column C are formatted with Wingdings 2.

How this formula works

At the core, this is just an IF statement set up to return a capital P when TRUE:

=IF(logical_test,"P","")

If the test returns FALSE, the formula returns an empty string (""). To display "P" as a check mark, cells in column C are formatted with Wingdings 2.

For the test, we are using the COUNTIF function like this:

COUNTIF(allowed,B5)

COUNTIF checks if the value in B5 occurs in the named range "allowed". If the value is found, COUNTIF will return a positive number, if not, COUNTIF will return zero. This works perfectly as the logical test for IF.

With fixed values

The example above shows allowed values in a range of cells, but allowed values can also be hard-coded into the formulas as an array constant like this:

=IF(COUNTIF({"red","blue","green"},B5),"P","")