Sourav Kundu

Hi JOHN,
Thank you for sharing your problem with us. We have got a simple solution to your problem. You can use a VBA event for this purpose. Whenever you need a blank attendance sheet for a new month, you just have to change the value of the green-colored cell G5.
First, Right-click on your worksheet named Automated.
Then, click on View Code.

Then copy the following VBA code and paste it into the opening window:

Private Sub Worksheet_Change(ByVal Target As Range)
   
    If Not Intersect(Target, Me.Range("G5")) Is Nothing Then
   
      Me.Copy After:=Sheets(Sheets.Count)
      Me.Range("E11:AI100").ClearContents
    End If
End Sub

Now, go back to the Automated sheet and insert Daily Attendences.

Now, write something in the G5 cell say N, and press Enter.
Instantaneously, the Automated sheet will be cleared and will create a new sheet Automated (2).

Now, go to the Automated (2) sheet and you can see the previous attendance record has been copied to this sheet.

The original worksheet is blank and is ready to take instructions from scratch.

Note: You can change the green color cell reference from the VBA code.

We hope that we have provided a reasonable solution to your problem. If you have more queries you can always mention them in the comment section.

Regards,
Sourav Kundu
Exceldemy.

Hello SUNZEEV,
Thank you for your comment. Here, we are providing two revised formulas to meet your requirements.
For simplicity, we have modified the dataset with two additional Company names.
To get all the device names for each company, write the following formula in E6 and press Enter.
=IFERROR(INDEX($B$5:$C$19,SMALL(IF($B$5:$B$19=E5,(ROW($B$5:$B$19)-ROWS(B1:B4)),""),(ROW($B$5:$B$19)-ROWS(B1:B4))),2),"")
You will get all the results of Samsung at once.
Now, drag the E6 cell to the right to H6 cell. You will get your desired results.

Another way is to use the FILTER function. For this method, write the following formula in E6 and press Enter.
=FILTER(C5:C19,B5:B19=E5)
Now, drag the E6 cell to the right to the H6 cell. You will get your desired results. The FILTER function is available on Excel 365 only.

You can download the workbook from the link below.
Answer.xlsx
We hope these methods will satisfy your queries. If you have further questions, you are always welcome.

Regards,
Sourav
ExcelDemy.

Hi NN,
Thank you for sharing your problem with us. We have got a solution to your requirement. With this solution, whenever you need a blank record, you just have to change the value of the yellow-colored cell. You have to use a VBA code in the worksheet for the purpose.
First, Right-click on your worksheet named Template.
Then, click on View Code.

Then. copy the following VBA code and paste it into the opening window:

Private Sub Worksheet_Change(ByVal Target As Range)
    ' Check if the changed cell is D3
    If Not Intersect(Target, Me.Range("D3")) Is Nothing Then
      ' Copy the entire sheet
      Me.Copy After:=Sheets(Sheets.Count)
      ' Clear contents of range D6:AH1006
      Me.Range("D6:AH1006").ClearContents
    End If
End Sub

Now, go back to your workbook and insert some Employee Leaves.
Now, write something say N, and press Enter.

The code will create a new worksheet containing the Employee Leaves records.

Now, go back to the previous worksheet.
The original worksheet is blank and is ready to take instructions from scratch.

Note: You can change the Yellow color cell reference from the VBA code.
You can download the updated template with the VBA code from below:
Answer.xlsm
We hope that we have provided a reasonable solution to your problem. If you have more queries you can always mention them in the comment section.

Regards,
Sourav Kundu
Exceldemy.

Hello, RUSSEL.
Thank you for sharing your problem with us. We have provided 2 different ways to solve your problem.
For example, we have a dataset of Product, Size, and Price. We will find the total price of the Small Shirts.
Method-1:
For this, write the following formula in the F9 cell and press Enter.
=IFERROR(INDEX($D$5:$D$15, SMALL(IF(($G$4=$B$5:$B$15)*($G$5=$C$5:$C$15), ROW($D$5:$D$15)-ROW($D$5)+1), ROWS($1:1))), "")
And, you will get the first Price value.
Then Drag the F9 formula downwards to get other Price values that meet the criteria.

You can see that we have got two Price values that meet the multiple criteria.
Now, you can use the SUM function to get the total Price value.

Method 2:
Write the following formula in F9 and press Enter.
=SUM(IFERROR(FILTER(D5:D15, (G4=B5:B15)*(G5=C5:C15)), ""))
And, you will get the total Price value with multiple criteria.

The FILTER function used in this method is only available for Microsoft 365 and Excel 2021.
Solution to Your Problem:
You can use the formula below to solve your problem:
=SUM(IFERROR(FILTER($G$20:$G$52, ($B$20:$B$53=$B3)*($E$20:$E$52=$D3)*($F$20:$F$52=CH$1)*($A$20:$A$53=$A3)*($D$20:$D$52=$E3)), ""))
We could have given you the exact formula if we had your dataset. Let us know if your problem is solved.
Regards,
Sourav
ExcelDemy.

Hi VIJAY,
Thank you for sharing your problem with us. As you wanted to determine each employee’s shift assuming that, their holiday can be any weekday, we have modified the attendance sheet. We hope this solution will meet your requirement.

1. First, you have to create a new table that will include the preferred work shifts of each employee.
2. For simplicity, we are going to format these cells based on text value.
3. So, select the data range F10:L14.

4. Now, from the Home tab go to Conditional Formatting and Select Format only cells that contain.
5. Then you have to set Yes as Specific Text.
6. Also, choose green as the format color.
7. Then, press the OK button.

8. Similarly, create another condition that if the cell contains the specific text No, the cell will be formatted as red.

9. Your selected cells should still be blank.
10. Now, to indicate the work shift, write Yes or No in these formatted cells.
11. And, you can see the cells are instantaneously formatted with green and red colors (You can also use Yes or No from the drop-down box instead of writing them manually).

11. Now, go to the attendance sheet and suppose, there is no condition in this Shift table.
12. Select the F19 cell and create a new condition.

13. You have to select Use a formula to determine which cells to format as the New Formatting Rule.
14. Then, write the following formula in the formula box.
=INDEX($B$9:$L$14, MATCH($B19,$B$9:$B$14,0),MATCH(F$18,$B$9:$L$9,0))="No"
15. Also, select a reddish format color and press OK.

16. Then, use the Fill Handle to copy the conditional formatting for all cells as shown in the following image.
17. You can see, the Attendance table has been marked by each employee’s preferred off days.

18. Now, you can select Present (P) or Absent (A) for each employee.

You may need to modify the conditional format formula according to your data table. Be careful about relative and absolute referencing. You can also check the Excel file from the link below:
Answer.xlsx
We hope this comment has been useful to you. Have a nice day.
Regards
Sourav
Exceldemy.

Hey Yann, we have tried your formula. But unfortunately, this formula is not working as you said it would. It gives a Value error instead.