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

 

[Stefi]

ry this formula:

=IF(Sheet1!C7="x",TEXT(Sheet1!C6,"dd.mmm")&",","")&IF(Sheet1!D7="x",TEXT(Sheet1!D6,"dd.mmm")&",","")&IF(Sheet1!E7="x",TEXT(Sheet1!E6,"dd.mmm")&",","")&IF(Sheet1!F7="x",TEXT(Sheet1!F6,"dd.mmm")&",","")&IF(Sheet1!G7="x",TEXT(Sheet1!G6,"dd.mmm")&",","")&IF(Sheet1!H7="x",TEXT(Sheet1!H6,"dd.mmm")&",","")&IF(Sheet1!I7="x",TEXT(Sheet1!I6,"dd.mmm")&",","")


--
Regards!
Stefi



„terilad†ezt írta:

 

[terilad]

Thanks Stefi, it works a treat only prob I have is that the name of the sheet
this formula is in has to be the same as a cell next to the x's with a
persons name in it, so when I place J Bloggs in Cell A8 the crosses come into
cells C9 and D9 this has to report to a cell with sheet name J Bloggs, can
you help with this?

Many many thanks

Mark

 

[Stefi]

Do you mean that you have a sheet with this structure
A B C D E F G
H I
1 header
2 name1 Mon Tue Wed Thu Fri Sat Sun
3 x x x
4 name2
5
6 name3
and you want to create a new sheet for each name with the formula in A1? A
macro can do it but you should give exact details, e.g. what should happen
when re-running the macro and sheet name1 already exists (are there other
data on it to keep, etc.).

--
Regards!
Stefi



„terilad†ezt írta:

 

[terilad]

Hi this is what i'm looking for but I already have the sheets created by
employee name.

Mark

 

[Stefi]

Enter this helper formula in an empty cell in name sheets, in my example this
is C1 cell:

=MATCH(MID(CELL("filename",A1),SEARCH("]",CELL("filename",A1))+1,1024),Sheet1!A:A,0)

and enter this formula in A1 in name sheets:

=IF(INDIRECT(ADDRESS($C$1+1,3,4,1,"Sheet1"))="x",TEXT(INDIRECT(ADDRESS($C$1,3,4,1,"Sheet1")),"dd.mmm")&",","")&IF(INDIRECT(ADDRESS($C$1+1,4,4,1,"Sheet1"))="x",TEXT(INDIRECT(ADDRESS($C$1,4,4,1,"Sheet1")),"dd.mmm")&",","")&IF(INDIRECT(ADDRESS($C$1+1,5,4,1,"Sheet1"))="x",TEXT(INDIRECT(ADDRESS($C$1,3,5,1,"Sheet1")),"dd.mmm")&",","")&IF(INDIRECT(ADDRESS($C$1+1,6,4,1,"Sheet1"))="x",TEXT(INDIRECT(ADDRESS($C$1,6,4,1,"Sheet1")),"dd.mmm")&",","")&IF(INDIRECT(ADDRESS($C$1+1,7,4,1,"Sheet1"))="x",TEXT(INDIRECT(ADDRESS($C$1,7,4,1,"Sheet1")),"dd.mmm")&",","")&IF(INDIRECT(ADDRESS($C$1+1,8,4,1,"Sheet1"))="x",TEXT(INDIRECT(ADDRESS($C$1,8,4,1,"Sheet1")),"dd.mmm")&",","")&IF(INDIRECT(ADDRESS($C$1+1,9,4,1,"Sheet1"))="x",TEXT(INDIRECT(ADDRESS($C$1,9,4,1,"Sheet1")),"dd.mmm")&",","")



--
Regards!
Stefi



„terilad†ezt írta: