Search that will return sheet names when specific cell is empty or filled

[zachyfarms]

I am trying to streamline employee course completion records for my company. I have 20 plus sheets and each sheet is named with an individual employees name. Each sheet is identical with column A holding course titles and column b that is blank for not completed or it is filled with a date if the course was completed. I'm trying to build a search box or some search function that if I type a specific course name, it will check all the sheets for that course name. Then it will verify if there is a date in the B column for that course and return results listing all the sheets names of those who haven't taken the course. I would also like to create the same search but the opposite to search who all has completed a certain courses. Maybe since each sheet is identical i don't need to reference the course name and only search the associated date cell in column B to get the results.

I did try some count functions but they only return a count of the sheets not the names.
Again I am very new to excel and have only learned some basics like conditional formatting and table building. Below is what each sheet looks like and on a separate sheet labels totals I want to process all these searches.
Never mind, I couldn't even figure out how to paste picture of a worksheet.

Thank you in advance for any help.

<colgroup><col><col span="5"></colgroup><tbody></tbody>

 

[My Aswer Is This]

I think this would require Vba.

And you said:

and return results listing all the sheets names of those who haven't taken the course.

But you did not say return the results where.

Do you want the results returned to sheet named "Master" Column (A) for if completed and Column (B) for not completed?

If not provide more details.
​

 

[zachyfarms]

The list of names from a search can populate anywhere on my Totals page. I didn't call it master and it does resemble all the other pages. I was just going to build a search box that search all the other pages and populates list of sheets names that fit the criteria.

thank you

 

[My Aswer Is This]

I asked for specifics but you gave none.

So are you saying you wants the results in a sheet named "Totals"

And if course has been completed put sheet name in column A of sheet named "Totals" and if not completed put sheet name in column B of sheet named "Totals"

And we will be searching all the sheets in the workbook except for sheet named "Total"

And I'm not sure why you say I need to:

I was just going to build a search box

What is a search box?

I suggest you enter the search value in Range("C1") of sheet named "Totals" and when you enter a value in That range the script will run.

I cannot help you without the specifics I'm asking for.

​

 

[zachyfarms]

Yes that is exactly what I am looking for. Type the name of a course in C1 causing a script to run for all sheets. List of all sheets with that course completed will populate on column A and those not completed in column B.

 

[My Aswer Is This]

Try this:
Put this script into a button on sheet named Totals
Put the search value in Range("C1") of sheet named Totals

You will see in the script at the top I provided a way for you to modify the sheet name.

When you press the button you put the script in the script will search all sheets column A for the name you entered in Range("C1") of sheet named "Totals"

Sub Search_For_Course()
'Modified  3/27/2019  11:4827 PM  EDT
Application.ScreenUpdating = False
Dim SearchString As String
Dim SearchRange As Range
Dim SN As String
SN = "Totals" '[COLOR=#ff0000]Modify this name if needed.[/COLOR]
SearchString = Sheets(SN).Cells(1, 3).Value
Dim lastrow As Long
Dim lastrowa As Long
Dim lastrowb As Long
lastrowa = Sheets(SN).Cells(Rows.Count, "A").End(xlUp).Row + 1
lastrowb = Sheets(SN).Cells(Rows.Count, "B").End(xlUp).Row + 1
Dim i As Long
    For i = 2 To Sheets.Count
        With Sheets(i)
            lastrow = .Cells(Rows.Count, "A").End(xlUp).Row
            Set SearchRange = .Range("A1:A" & lastrow).Find(SearchString, LookIn:=xlValues, lookat:=xlWhole)
                If SearchRange Is Nothing Then MsgBox SearchString & "  Not Found": Exit Sub
                If SearchRange.Offset(, 1).Value = "" Then Sheets(SN).Cells(lastrowb, 2).Value = Sheets(i).Name: lastrowb = lastrowb + 1
                If SearchRange.Offset(, 1).Value <> "" Then Sheets(SN).Cells(lastrowa, 1).Value = Sheets(i).Name: lastrowa = lastrowa + 1
        End With
    Next
Application.ScreenUpdating = True
End Sub