IF formula to read whole column for certain text

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?

Thank you your formula worked and I will use it for further spreadsheets.

.
Here is a VBA approach:

Option Explicit

Sub Button1_Click()
    Dim Rws As Long, Rng As Range, ws As Worksheet, sh As Worksheet, c As Range, x As Integer
    Set ws = Worksheets("Sheet1")  'specify sheet name here to paste to
    x = 2   'begins pasting in Sheet 1 on row 2
    Application.ScreenUpdating = 0
    For Each sh In Sheets
        If sh.Name <> ws.Name Then
            With sh
                Rws = .Cells(Rows.Count, "D").End(xlUp).Row 'searches Col D all sheets
                Set Rng = .Range(.Cells(1, "D"), .Cells(Rws, "D"))
                For Each c In Rng.Cells
                    If c.Value = "No" Then  'searches for term NO
                        c.EntireRow.Copy Destination:=ws.Cells(x, "A")
                        x = x + 1
                    End If
                Next c
            End With
        End If
    Next sh
End Sub