Hi, I'd be grateful if I could secure some help on this please...I've been struggling with it ...
I have a database spread over several sheets, each containing data attributable to employees. One sheet contains "Training Data", the other "Absence Data" and another "Personal Data". I have a master sheet that combines all of the elements on these sheets together to produce an overall record for the employee. I've used VLOOKUP to successfully combine most of the data thus far, using the unique identifier of the "Employee Number".
On the "Absence Data" worksheet, I am able to obtain the total number of episodes of absence for a particular employee. However, I am having difficulty in extracting the number of absences for a particular employee for a particular reason (in this case "S10 Anxiety/stress/depression/other psychiatric illnesses"). Ideally I'd like to be able to search for just the reason code for absence rather than the full text, ie S10.
The "Employee Number" exists in A2 on the master sheet and in C2 on the "Absence Data". The "Absence Data" worksheet has a range of A2:AE13068 so there's a lot of data there. The specific absence reasons are recorded in Column E. I've tried a combination of COUNTIFS, VLOOKUP. One of my rudimentary formulas is below, but it just returns a blank. removing the IFERROR statement, returns #VALUE!
=IFERROR(COUNTIFS('Absence Data'!$A2:$AE13068,$A2,'Absence Data'!E:E,"*S10*"),"")
Can you please help me shed some light on this please.
Many thanks
Rob
I have a database spread over several sheets, each containing data attributable to employees. One sheet contains "Training Data", the other "Absence Data" and another "Personal Data". I have a master sheet that combines all of the elements on these sheets together to produce an overall record for the employee. I've used VLOOKUP to successfully combine most of the data thus far, using the unique identifier of the "Employee Number".
On the "Absence Data" worksheet, I am able to obtain the total number of episodes of absence for a particular employee. However, I am having difficulty in extracting the number of absences for a particular employee for a particular reason (in this case "S10 Anxiety/stress/depression/other psychiatric illnesses"). Ideally I'd like to be able to search for just the reason code for absence rather than the full text, ie S10.
The "Employee Number" exists in A2 on the master sheet and in C2 on the "Absence Data". The "Absence Data" worksheet has a range of A2:AE13068 so there's a lot of data there. The specific absence reasons are recorded in Column E. I've tried a combination of COUNTIFS, VLOOKUP. One of my rudimentary formulas is below, but it just returns a blank. removing the IFERROR statement, returns #VALUE!
=IFERROR(COUNTIFS('Absence Data'!$A2:$AE13068,$A2,'Absence Data'!E:E,"*S10*"),"")
Can you please help me shed some light on this please.
Many thanks
Rob