Daily production sheet and Attendance sheet integration

Question

I have 3 sheets. The first sheet is named ACTUAL HRS. It has a table that starts from A to F. Column A is the User ID and Columns C1, D1, and E1 are headers for dates from Nov 01 to Nov 04. The values in the cells are the number of hours w/c the agent had worked from Nov 01 to Nov 04. If the cell is empty it means the agent is Absent for that specific day.

The next sheet is named DAILY PRODUCTION. It has the same Columns as the first sheet. But instead of time as the value, the value is the number of leads generated by the agent on a specific day. If the cell is empty, it means that the agent has not produced any leads.

Now on the 3rd sheet. I want to Create a table with the same columns as the first 2 sheets. But the values for each cell will be the value of the lead generated for the day and if the agent is absent (empty cell on ACTUAL HRS sheet, meaning that agent is Absent), the cell will be "Absent". if the agent is Present with Production the return value should be the number of leads produced.

Also, on the 3rd sheet, I only have specific USER IDs to be included. Meaning, I will input the USER IDs on the 3rd sheet. enter image description here

enter image description here

I use IF and VLOOKUP function but I was not getting the correct values. I want the values to match the USER ID but it's not reflecting correctly based on my formula.

Answer 1

I would recommend trying the 'IFS' function which is similar to the 'IF' function except here we can input multiple logical tests. I recommend this because we can say your situation has 2 'ifs' being:

1. If hours on ACTUAL HRS sheet is empty -> Absent
2. If hours on ACTUAL HRS sheet NOT NULL -> #leads

So we can try the following: =IFS('ACTUAL HRS'!B2 = "","Absent",'ACTUAL HRS'!B2>=1,DAILYPRODUCTION!B2)

The first portion: 'ACTUAL HRS'!B2 = "","Absent" This refers to if the HRS sheet has a blank cell, we return "Absent"

The second portion: 'ACTUAL HRS'!B2>=1,DAILYPRODUCTION!B2 This refers to if the HRS sheet has a cell that is 1 or greater, we return the same cell position from the PROD sheet

We can expand this formula in all directions to cover the grid of IDs x Dates you've outlined which should leave us with a table in sheet 3 that is populated with either #leads or the string "Absent".