Macro or Formula
 

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

Macro or Formula
 

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

Interested in that method?

--
Biff
Microsoft Excel MVP


"terilad" wrote in message
...
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

Macro or Formula
 

Yes, ok will try that one.

Can you give me a formula for it?

Regards

Mark

"T. Valko" wrote:

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

Interested in that method?

--
Biff
Microsoft Excel MVP


"terilad" wrote in message
...
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



.

Macro or Formula
 

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),COLUM NS($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

--
Biff
Microsoft Excel MVP


"terilad" wrote in message
...
Yes, ok will try that one.

Can you give me a formula for it?

Regards

Mark

"T. Valko" wrote:

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

Interested in that method?

--
Biff
Microsoft Excel MVP


"terilad" wrote in message
...
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



.

Macro or Formula
 

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" wrote:

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),COLUM NS($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

--
Biff
Microsoft Excel MVP


"terilad" wrote in message
...
Yes, ok will try that one.

Can you give me a formula for it?

Regards

Mark

"T. Valko" wrote:

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

Interested in that method?

--
Biff
Microsoft Excel MVP


"terilad" wrote in message
...
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



.



.

Macro or Formula
 

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

--
Biff
Microsoft Excel MVP


"terilad" wrote in message
...
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" wrote:

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),COLUM NS($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

--
Biff
Microsoft Excel MVP


"terilad" wrote in message
...
Yes, ok will try that one.

Can you give me a formula for it?

Regards

Mark

"T. Valko" wrote:

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

Interested in that method?

--
Biff
Microsoft Excel MVP


"terilad" wrote in message
...
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



.



.