Macro or Formula

[terilad]

Hi,

I need help with a problem.

I have a range of cells C6 to I6 tith the date in them Mon to Sun, below
these cells I have input in some of the cell an X to mark the days a person
is taking a holiday. so Wed, Thurs, Fri the cells below are marked with an X
to indicate a holiday, what I am looking to do is report on another sheet the
dates of the holiday this person is taking, so it would report 10th, 11th and
12th Feb 2010 in cell A1 to indicate the dates of the holiday.

Can anyone help me with this i'm stuck.

Many thanks

Mark

 

[T. Valko]

This would be easier to do (with a formula) if you put each date in a
separate cell.

Interested in that method?

 

[terilad]

Yes, ok will try that one.

Can you give me a formula for it?

Regards

Mark

 

[T. Valko]

Ok, I'm assuming in your schedule table you have a list of employee names.

So, C6:I6 are the dates, B7:B10 are employee names. C7:I10 matrix with some
cells marked with X.

A2 = some employee name

Enter this formula in B2. This will return the count of X's for the employee
in A2:

=COUNTIF(INDEX($C$7:$I$10,MATCH($A2,$B$7:$B$10,0),0),"x")

Enter this array formula** in C2 and copy across to I2. This will return the
holiday dates for the employee in A2:

=IF(COLUMNS($C2:C2)>$B2,"",SMALL(IF(INDEX($C$7:$I$10,MATCH($A2,$B$7:$B$10,0),0)="x",$C$6:$I$6),COLUMNS($C2:C2)))

Format as Date

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.

Here's a small sample file that demonstrates this.

Sample1.xls 14kb

http://cjoint.com/?dfeImjWlgF

 

[terilad]

Many thanks Biff,

Just to another pain, is there a way to ling the name to a sheet name as I
have sheets named with employees names and the input sheet contains the input
as the sample but can the dates of hoidays be put into the individuals sheet?

Many thanks

Mark

 

[T. Valko]

If I understand what you want then just put the formula on each persons own
sheet.