Can you use a User Defined Function (UDF)? If so, this should do the trick
for you. A UDF is a macro in the form of a Function that can be used in a
worksheet just like built-in Excel worksheet functions.

To insert this code into the workbook, press [Alt]+[F11] to open the VB
Editor, then choose Insert | Module to create a new code module. Copy the
code below and paste it into the module. Close the VB Editor.

Anywhere that you want to see the list, place this formula into the cell,
and format the cell to allow word-wrapping and make the row tall enough to
see multiple entries:
=wherearependings()

I hope this helps some. Here is the code to copy and paste:

Public Function WhereArePendings()
Dim anySheet As Worksheet
Dim testRange As Range

Application.Volatile
WhereArePendings = "None Pending"
For Each anySheet In Worksheets
Set testRange = anySheet.Range("M5")
If UCase(Trim(testRange)) = "PENDING" Then
If WhereArePendings = "None Pending" Then
WhereArePendings = anySheet.Name
Else
WhereArePendings = _
WhereArePendings & vbLf & anySheet.Name
End If
End If
Next
Set testRange = Nothing
End Function


"TUNGANA KURMA RAJU" wrote:

In all my w/sheets of my active w/book ,range("M5') has a value "pending" or
"completed". I need a function that returns list of those work sheet names
with"pending" value in range("M5").
Thank you all excel experts in advance.