Search code examples
excelcheckboxdatestamp

Keeping track of "No Shows" on roster

Currently I am making a schedule of class times where the secretary adds names to the list for however many seats are available for that room, and it shows whether or not they have passed the test already. The managers would like a count of how many times the physician may have no showed. Column A is the seat number (plays no real role), column B is the name slot, which pulls a searchable list from a master list, with the "=Cell("contents")" trick because there are too many for a straight drop down. Column C is at VLOOKUP to check their current test status to help not double book. And finally, Column D is a checkbox if they no show.

enter image description here

I have a separate sheet that is keeping track of these no shows, it records the name, a count of 1, and the date they skipped.

enter image description here

Question 1, is there a way to not have to make each checkbox individually and link each individually? There's 8 weeks of class with 60+ seats.

Question 2, is there a way to make it add rows to this sheet only if checked off so there isn't 900 blank rows for a pivot table?

Code used on "NoShow" sheet:

=IF(Schedule!D5=TRUE,Schedule!B5,"")
=IF(A2<>"","1","")
=IF(Schedule!D5=TRUE,TODAY(),"")
Solution

  • This can be done easier with Power Query. In this example, I have:

    • One table on each worksheet, for each training date. No shows are indicated with "Yes".
    • Each table is named t_ and the table name.

    Then Power Query consolidates all of the tables into one and produces one table showing all of the consolidated records, that is summarized with a pivot table, and another with unique names, that can be used for your drop-down menu.

    When you have a new date, just add a new worksheet with a table for that date, fill in the info and Refresh the calculations.

    enter image description here

    enter image description here

    Here is the table of consolidated data...

    enter image description here

    Here is the pivot that counts the no shows...

    enter image description here

    To get the summary table... After you set up your tables, insert a blank query by going to Data > Get and Transform Data > Get Data > From Other Sources > Blank Query.

    Then click Advanced Editor, delete any existing text and paste this:

    let
    Source = Excel.CurrentWorkbook(),
    #"Filtered Rows" = Table.SelectRows(Source, each ([Name] <> "Summary")),
    #"Expanded Content1" = Table.ExpandTableColumn(#"Filtered Rows", "Content", {"Seat Number", "Name of Physician", "No Show?"}, {"Seat Number", "Name of Physician", "No Show?"}),
    #"Duplicated Column" = Table.DuplicateColumn(#"Expanded Content1", "Name", "Name - Copy"),
    #"Removed Columns" = Table.RemoveColumns(#"Duplicated Column",{"Seat Number"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Name - Copy", "Date"}}),
    #"Extracted Text After Delimiter" = Table.TransformColumns(#"Renamed Columns", {{"Date", each Text.AfterDelimiter(_, "_"), type text}}),
    #"Changed Type" = Table.TransformColumnTypes(#"Extracted Text After Delimiter",{{"Date", type date}}),
    #"Reordered Columns" = Table.ReorderColumns(#"Changed Type",{"Name", "Date", "Name of Physician", "No Show?"}),
    #"Renamed Columns1" = Table.RenameColumns(#"Reordered Columns",{{"Name", "Table Name"}})
in
    #"Renamed Columns1"

    Then click Close and Load To > New Worksheet.

    To get the unique names table.... Follow the same steps above, but in a new blank query, paste this text...

    let
    Source = Excel.CurrentWorkbook(),
    #"Filtered Rows" = Table.SelectRows(Source, each ([Name] <> "Summary")),
    #"Expanded Content1" = Table.ExpandTableColumn(#"Filtered Rows", "Content", {"Seat Number", "Name of Physician", "No Show?"}, {"Seat Number", "Name of Physician", "No Show?"}),
    #"Duplicated Column" = Table.DuplicateColumn(#"Expanded Content1", "Name", "Name - Copy"),
    #"Removed Columns" = Table.RemoveColumns(#"Duplicated Column",{"Seat Number"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Name - Copy", "Date"}}),
    #"Extracted Text After Delimiter" = Table.TransformColumns(#"Renamed Columns", {{"Date", each Text.AfterDelimiter(_, "_"), type text}}),
    #"Changed Type" = Table.TransformColumnTypes(#"Extracted Text After Delimiter",{{"Date", type date}}),
    #"Reordered Columns" = Table.ReorderColumns(#"Changed Type",{"Name", "Date", "Name of Physician", "No Show?"}),
    #"Renamed Columns1" = Table.RenameColumns(#"Reordered Columns",{{"Name", "Table Name"}}),
    #"Removed Columns1" = Table.RemoveColumns(#"Renamed Columns1",{"No Show?", "Date", "Table Name"}),
    #"Removed Duplicates" = Table.Distinct(#"Removed Columns1")
in
    #"Removed Duplicates"

    Then Close and Load To > New Worksheet.

    Then you can select the data in summary table and Insert Pivot Table. Add the names to the Rows section and the No Shows to the Values section. In the Row Labels column header, click Value Filters > Greater Than 0 (to remove the blanks). With the pivot table, you can double-click on the number of no shows and a new worksheet will be created, showing you where that calculation came from, so there's not need for the hyperlink.

    enter image description here