I have created an excel workbook with 30 worksheets. Within the 30 worksheets is a list of tasks that are created everyday. In column D of each worksheet is the title Done yes/no. So when the task is completed by the end user they are to change the word from yes to no.

I have created a cover sheet that lists all the worksheets by their name. On the cover sheet against each worksheet name I am trying to create a formula that reads the individual worksheet and every time the word in Column D appears with the text "no" it will populate on the coversheet with the word "NEW". It is designed to highlight which worksheets has a new task listed to avoid the need on clicking every worksheet to see if a new task has been created.

I have written the following formula but it is not reading the whole column to search for the word "no". If the word "yes" appears in the row under the heading it responds with the answer "FALSE" although there are several rows with the answer "no" in them. I have labelled the column D with a range name hoping that would solve it but I am still getting the same response.

My formula reads: =IF(ActionBEL="no","NEW")
Column D = range name "ActionBEL"

Is there a different style of formula I need to write to read the whole column and respond with the text "NEW" if there is a "no" sitting anywhere in the column?