Forum Discussion
Getting functions to recognize symbols.
I am trying to create a function that will examine cells on several different workbook pages that will either contain a checkmark symbol (from the Insert, Symbols menu) or be blank. If all the cells being examined are populated with checkmarks it would display the word "Complete" in a cell on the cover workbook. If all of the examined cells have not been checked off the cell on the cover workbook would remain blank. I used the “=IF” function to compare some of the examined cells and if they were equal (populated with a checkmark symbol) it would display “Complete”. Unfortunately if none of the cells contain a checkmark symbol they are also all equal and it also returns a "Complete" to the cell on the cover page.
Any help would be appreciated.
4 Replies
Hi Mathew,
If you check your cells against checkmarks why don't you compare them with another cells, not directly with checkmark character? Or don't calculate number of checkmarks in cells where you expect them, like =COUNTIF(range, UNICHAR(10003)), or which exactly checkmark symbol do you use...
The check mark that I am using in the cell to signify completed work is
ü
The checkmark text that appears in the text entry field above the worksheet is actually a lower case u with two dots above it. I think it corresponds with the checkmark symbol in wingding font (Wingding 252).
The function that I originally came up with was =IF(Insulation!I50=Coatings!I39,"Complete",""). This works if there are only two cells that i want to compare. Unfortunately I need for it to compare 3 or more cells. It also returns the "Complete" result if all the cells are empty which makes them equal but is not the result I am looking for.
What I eventually ended up doing was creating a separate sheet in my workbook and performing some of the functions there.
Hi Mathew,
1) To be sure with which symbol you compare you may check the code of the symbol by UNICODE() and after that use UNICHAR() with returned code to compare.
2) To compare 3 cells and exclude the case when all are empty you may use
=IF(AND(A1=C1,C1=F1)*LEN(A1),"Complete","")
AND() returns TRUE (=1) if all cells are with same value and LEN() returns 0 (=FALSE) if they are empty, otherwise TRUE. If cells are not equal it doesn't matter what is in A1.
