turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- 175K Members
- 1,626 Online
- 43.7K Conversations

- Home
- :
- Excel
- :
- General Discussion
- :
- IFERROR - HLOOKUP- IF formula error

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

SOLVED
## IFERROR - HLOOKUP- IF formula error

- Subscribe to RSS Feed
- Mark Conversation as New
- Mark Conversation as Read
- Pin this Conversation for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

Highlighted

data24365

Occasional Contributor

01-12-2018
09:06 PM
- last edited on
07-25-2018
10:45 AM
by
TechCommunityAP

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

01-12-2018
09:06 PM
- last edited on
07-25-2018
10:45 AM
by
TechCommunityAP

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

Labels:

14 Replies

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

01-12-2018 09:55 PM

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&"!,")))

*

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

01-13-2018 12:13 AM

Solution

@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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

01-13-2018 01:39 AM

The file is broken. It doesn't show the formulas, only the #NAME? error.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

01-13-2018 02:07 AM

Sorry Detlef for the broken file. May be because I edited the file in libre office (?)

Your solution works superbly now.

Thank you very much

Have a nice day

JIM

Your solution works superbly now.

Thank you very much

Have a nice day

JIM

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

01-13-2018 03:16 AM

Jim,

glad ist worked out in the end.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

01-13-2018 06:59 AM

Dear Detlef,

Sorry to trouble you further. Your formula to pull data values to the attendance consolidation sheet is superb, thank you for the effort.

Some problem still exists - spotted them after doing a through testing with inputs.

Not all values from the attendance entry sheet are being updated in the corresponding rows in the attendance consolidation sheet. Please help.

Thank you

JIM

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

01-13-2018 10:29 AM

Please be more specific.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

01-13-2018 05:10 PM

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

01-13-2018 06:52 PM

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&",")))

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

01-13-2018 09:03 PM

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

01-13-2018 11:45 PM

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&","))))

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

01-13-2018 11:56 PM

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

01-14-2018 12:03 AM

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

01-14-2018 01:42 AM

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

Share

Popular

Learning Resources

Programs

Values

- Contact us
- Privacy & cookies
- Terms of use
- Trademarks
- About our ads
- © 2017 Microsoft