Excel fuction - help required in Attendance Sheet?

**NonStopLeo** · 03-10-2010, 05:13 AM

I have an excell sheet (Attendance Sheet) in which I have following data
In row 1, I have dates of a month like 1, 2, 3,.......30 etc (from column B to Column AE) 1-30 dates
In second row, I have following data
Column A Name of employee like ABC, XYZ, KMJ etc (one employee in each row)
Column B attendance of respective employee against each date like P in case of present & A for absent, L for leave etc
Column C Same as column B, so on till column AE.
In Column AF, I have countif formula which counts number of Presents (P) during the month of respective employee
In Column AG, I have countif formula which counts number of absents (A) of respective employee
In Column AH, I have countif formula which counts number of Leaves (L) of respective employee
Till here I have no problem.
Now In column AI to onwards, I want the formula which shows that dates in which respective employee was not present i.e was on leave or absent.
Please help me in this. Thanks

Attached is my sheet for your reference, Please help.

**jrussell** · 03-10-2010, 09:36 AM

It's tough to make a formula that puts results into a variable number of cells.

Have you thought about showing this data another way? You could use conditional formatting to highlight the "L"s and "A"s in columns B to AE. What exactly do you need to do with this data?

**NonStopLeo** · 03-11-2010, 06:04 AM

Please help if it can be done on some other sheet or somewhere else in the same workbook by using any function or macro. Please help.

**DonkeyOte** · 03-11-2010, 06:30 AM

Your narrative and sample file don't really tie out very well - see if the attached is along the lines of what you want.

In essence the attached uses the cumulative count of absence types to determine as to whether or not an absence date needs to be retrieved.
If so a SMALL Array is performed to calculate said date

**NonStopLeo** · 03-11-2010, 07:00 AM

Thanks for help, It can resolve my issue,
Please give me another help, If I add more days (Date 1 to Date 30) in this sheet, then how Can I update the formula.

**DonkeyOte** · 03-11-2010, 07:10 AM

Originally Posted by NonStopLeo

If I add more days (Date 1 to Date 30) in this sheet, then how Can I update the formula.

You shouldn't need to "update" the formula you simply need to create more of them (to the right)
(ie up to 30/31 assuming someone could be absent for an entire month)

If you have problems post back with a version that reflects your actual requirements - this will save all parties a lot of time.

**NonStopLeo** · 03-12-2010, 12:39 AM

Attached is my sheet, Please help me to put formula in this sheet. Thanks

**DonkeyOte** · 03-12-2010, 05:42 AM

You updated all the range references with exception of those that refer to the days and absences - ie B:AF rather than B:G (copy formula to the right for more results if required)
remember to reset the Array with CTRL + SHIFT + ENTER when you've completed the adjustments