Attendance Sheet Formula not working, please rectify it.

**NonStopLeo** · 03-15-2010, 08:13 AM

Formula in my attached sheet revert #NAME?, Please make it correct for me. Thanks

**NBVC** · 03-15-2010, 08:19 AM

Your post does not comply with Rule 1 of our Forum RULES. Your post title should accurately and concisely describe your problem, not your anticipated solution. Use terms appropriate to a Google search. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will be addressed according to the OP's experience in the forum: If you have less than 10 posts, expect (and respond to) a request to change your thread title. If you have 10 or more posts, expect your post to be locked, so you can start a new thread with an appropriate title.
To change a Title on your post, click EDIT then Go Advanced and change your title, if 2 days have passed ask a moderator to do it for you.

ALSO, no workbook attached.

**NonStopLeo** · 03-15-2010, 08:42 AM

I have attached the workbook now. Please help

**DonkeyOte** · 03-15-2010, 08:49 AM

1 - the formula is an Array and must be confirmed as such (CTRL + SHIFT + ENTER not Enter)

2 - presumably "P" stands for present and dates are not to be listed in which case you should subtract the value of Col AG from your initial test)

AI6:
=IF(COLUMNS($AI6:AI6)>(SUM($AD6:$AH6)-$AG6),"",SMALL(IF($B6:$AC6<>"P",$B$4:$AC$4),COLUMNS($AI6:AI6)))
confirmed with CTRL + SHIFT + ENTER

**NonStopLeo** · 03-16-2010, 04:46 AM

The formula is not working with me, it gives ERROR, please help me. Thanks

**DonkeyOte** · 03-16-2010, 05:05 AM

NonStopLeo - you're not really helping yourself here I'm afraid... in reality your last post is pretty pointless.

For people to best assist I would suggest you post a sample with the suggested formulae in place thereby illustrating the problems you're facing.

To reiterate though - you must ensure you commit the formula as an ARRAY (as indicated previously by virtue of red bold font).

**NonStopLeo** · 03-24-2010, 07:02 AM

Dear team, I have tried my level best to complete the formula, it is given accurate result but onething,
It is also giving the values of blanks cell, please help me, I dont want values of blank cells. Please help.

**DonkeyOte** · 03-24-2010, 07:16 AM

Rather than repeat myself I will simply quote from post # 4

Originally Posted by D.O

2 - presumably "P" stands for present and dates are not to be listed in which case you should subtract the value of Col AG from your initial test)

AI6:
=IF(COLUMNS($AI6:AI6)>(SUM($AD6:$AH6)-$AG6),"",SMALL(IF($B6:$AC6<>"P",$B$4:$AC$4),COLUMNS($AI6:AI6)))
confirmed with CTRL + SHIFT + ENTER

So though your setup has now changed ("P" column was AG now AJ) the above logic persists.

**NonStopLeo** · 03-24-2010, 07:58 AM

How can I exclude dates of blank cells which are also shown with this formula?

**DonkeyOte** · 03-24-2010, 08:02 AM

The formula provided (though not used) does what you want - you just need to adjust the column references.

There is really nothing more I can add to this discussion I'm afraid.