Forum Discussion
IFERROR - HLOOKUP- IF formula error
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
Please be more specific.
Hello Detlef,
Sorry for the trouble.
See if I enter a series of comma separated numbers from 1 to 72 in Attendance entry sheet B2, B3 etc then the consolidated sheet is getting updated only from roll nos 1 to 71 (column C) roll no 72 ie cell C74 is left out.
Thank you
JIM
Dear Detlef,
Sorry for the trouble, again I edited the file in Libre Office probably that caused the error.
Your formula works now, thanks a lot.
The file attached.
It was indeed a wonderful experience working with you.
Thank you very much
JIM
Again this a broken file showing no formulas but only #NAME?.
Thank you for the response.
I am enclosing the sheet after the formula update. #Name error
Also cells A42:B42 (ie, Saturday 1 - Saturday 7) does not figure in the formula.
Thank you
JIM
What does not work with your first formula? It seems alright to me.
For your second formula you shouldn't use a cell reference because it will only check B4.
=IF(VLOOKUP(C$1&" "&C$2,'Attendance entry'!$A$2:$B$40,2,FALSE)="H","H",SUMPRODUCT(('Attendance entry'!$A$2:$A$40=C$1&" "&C$2)*ISNUMBER(SEARCH(","&$A3&",",","&'Attendance entry'!$B$2:$B$40&","))))Hi Detlef,
Thank you very much.
On more thing: if I add in 7 hours on saturday in the attendance entry sheet will be the formula be like this (gave a try but not working)
SUMPRODUCT(('Attendance entry'!$A$3:$A$49=C$1&" "&C$2)*ISNUMBER(SEARCH(","&$A3&",",","&'Attendance entry'!$B$3:$B$49&",")))
Also I have added in one more functionality to account for mid week holidays (by inputting "H" against the corresponding hour in attendance entry sheet . The working formula for this is given below.
IF('Attendance entry'!$B$4="H","H",SUMPRODUCT(('Attendance entry'!$A$3:$A$41=C$1&" "&C$2)*ISNUMBER(SEARCH(","&$A3&",",","&'Attendance entry'!$B$3:$B$41&","))))
Thank you
JIM
Sorry, there was a typo in my formula:
=SUMPRODUCT(('Attendance entry'!$A$3:$A$41=C$1&" "&C$2)*ISNUMBER(SEARCH(","&$A3&",",","&'Attendance entry'!$B$3:$B$41&",")))