Forum Discussion

data24365's avatar
data24365
Copper Contributor
Jan 13, 2018
Solved

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 "Atte...
Formulas & Functions
IFERROR
  • data24365's avatar
    data24365
    Jan 13, 2018
    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

Detlef_Lewin's avatar
Detlef_Lewin
Silver Contributor
Jan 14, 2018

Again this a broken file showing no formulas but only #NAME?.

 

data24365's avatar
data24365
Copper Contributor
Jan 14, 2018

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

Resources