Forum Discussion
IFERROR - HLOOKUP- IF formula error
Hello,
I am trying to use excel to build an attendance consolidation sheet. Each day has 7 hours (1 through 7).
The user has to fill in the absentee roll numbers (separated by comma) in the "Attendance entry sheet".
This data is pulled into the "sort" sheet and separated using a TRIM-MID-SUBSTITUTE combination.
The sort sheet data is used to fill up the corresponding entry in the "consolidated sheet". If a person is absent, a "0" is marked against him/her in the corresponding hour else an "x". The problem I face now is that the IFERROR - HLOOKUP- IF formula used for the purpose is giving a #Value ! error. The sample for the first two persons on Monday 1st hour is highlighted.
Also I am not able to pull down the formula down the cell to replicate the same over the entire week from Monday to Friday.
File attached for reference.
Thank you for the patience
JIM
Detlef_Lewin wrote:
Jim,
first some preparations:
Remove the merged cells in "consolidated sheet" row 1 and type the weekday in every seven cells.
Sheet "sort" is not needed anymore.
This formula in "consolidated sheet" C3 and copy down and to the right:
=SUMPRODUCT(('Attendance entry'!$A$3:$A$41=C$1&" "&C$2)*ISNUMBER(SEARCH(","&$A3&",",","&'Attendance entry'!$B$3:$B$41&"!,")))*
Thank you for the reply Detlef.
I have done the corrections suggested by you. Now a #Name error shows up, anything related to the formula syntax?
Also will the row between Monday 7 and Tuesday 1 in the "Attendance entry" sheet matter?
File enclosed
Thank you for the help
Jim,
first some preparations:
Remove the merged cells in "consolidated sheet" row 1 and type the weekday in every seven cells.
Sheet "sort" is not needed anymore.
This formula in "consolidated sheet" C3 and copy down and to the right:
=SUMPRODUCT(('Attendance entry'!$A$3:$A$41=C$1&" "&C$2)*ISNUMBER(SEARCH(","&$A3&",",","&'Attendance entry'!$B$3:$B$41&"!,")))*
Detlef_Lewin wrote:
Jim,
first some preparations:
Remove the merged cells in "consolidated sheet" row 1 and type the weekday in every seven cells.
Sheet "sort" is not needed anymore.
This formula in "consolidated sheet" C3 and copy down and to the right:
=SUMPRODUCT(('Attendance entry'!$A$3:$A$41=C$1&" "&C$2)*ISNUMBER(SEARCH(","&$A3&",",","&'Attendance entry'!$B$3:$B$41&"!,")))*
Thank you for the reply Detlef.
I have done the corrections suggested by you. Now a #Name error shows up, anything related to the formula syntax?
Also will the row between Monday 7 and Tuesday 1 in the "Attendance entry" sheet matter?
File enclosed
Thank you for the help
The file is broken. It doesn't show the formulas, only the #NAME? error.
