Then in the Data Validation window, keep remaining in the Settings tab.
For this, select all the attendance cells.So, when you want to input the attendance data, you can’t insert any other values except the Type list. Now, you will set up a drop-down menu for the attendance ceblls. STEP 7: Set up Drop-down Menu for the Attendance Cells in Excel Now, the template will be like the screenshot below.Finally, copy and paste the values to the remaining cells of the row.IFERROR(IF(C6=””,1,MATCH(C6,Holidays,0)),0) : It denotes that when the IF condition can’t give any values then it will give an error value and the IFERROR function works to give the value 0 instead of Error!.IF(C6=””,1,MATCH(C6,Holidays,0) : IF Function denotes that if the value of cell C6 is blank then insert 1 else search that in the Holiday list.MATCH(C6,Holidays,0) : The MATCH function will search the value of C6 in the Holiday list.Now, type this formula into the Start Date cell.Click on this to open and select a month.Now, if you go to the month cell in the worksheet, you will see a drop-down option to open.And, type “= Month” in the Source option and press OK.then, select the “ List” option in the Allow menu.As an outcome, a window named “ Data Validation” will appear.Then, go to the Data tab and click on the Data Validation option.For this, you have to make a drop-down option in the cell to select a month. And the data for the whole year will be on the same worksheet. We want to make a template for tracking attendance where you can shift from one month to another easily. STEP 4: Insert Formula for Month, Start Date & End Date And insert the data of participants’ names and ids. Now, make columns and cells with the necessary things listed before. STEP 3: Make The Template Structure to Track Attendance Then, type “ Type” as the name and press OK.Lastly, select the Type cells and go to the Defined Name option.Then, type “ Holiday” as the name and press OK.Similarly, select the holiday cells and go to the Defined Name option.Here, give a suitable name for the list of cells. After that, you will see a window named “ New Name”.Then, go to the Formula tab > Defined Name option.To do this, select the cells of months.First, define a name for the list of the months.Defining the name will allow you to use the Data Validation tool to make a drop-down menu in the cells. You can add or remove holidays as per the calendar of your institution.Īfter inserting the necessary information, you have to define name for them. 💬 NOTES: I haven’t listed all the holidays in this workbook.
0 kommentar(er)
