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

zachyfarms

New Member
Joined
Mar 25, 2019
Messages
3
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.
[TABLE="width: 762"]
<colgroup><col><col span="5"></colgroup><tbody></tbody>[/TABLE]
 

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.
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.
 
Upvote 0
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
 
Upvote 0
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.

 
Last edited:
Upvote 0
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.
 
Upvote 0
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"

Code:
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
 
Upvote 0

Forum statistics

Threads
1,223,888
Messages
6,175,219
Members
452,619
Latest member
Shiv1198

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top