| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Macro or Formula
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: 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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Macro or Formula
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" wrote: 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: 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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Macro or Formula
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: 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" wrote: 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: 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 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Macro or Formula
Hi this is what i'm looking for but I already have the sheets created by
employee name. Mark "Stefi" wrote: 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: 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" wrote: 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: 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 |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Macro or Formula
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",T EXT(INDIRECT(ADDRESS($C$1,3,4,1,"Sheet1")),"dd.mmm ")&",","")&IF(INDIRECT(ADDRESS($C$1+1,4,4,1,"Sheet 1"))="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(INDIRE CT(ADDRESS($C$1+1,7,4,1,"Sheet1"))="x",TEXT(INDIRE CT(ADDRESS($C$1,7,4,1,"Sheet1")),"dd.mmm")&",","") &IF(INDIRECT(ADDRESS($C$1+1,8,4,1,"Sheet1"))="x",T EXT(INDIRECT(ADDRESS($C$1,8,4,1,"Sheet1")),"dd.mmm ")&",","")&IF(INDIRECT(ADDRESS($C$1+1,9,4,1,"Sheet 1"))="x",TEXT(INDIRECT(ADDRESS($C$1,9,4,1,"Sheet1" )),"dd.mmm")&",","") -- Regards! Stefi €˛terilad€¯ ezt Ć*rta: Hi this is what i'm looking for but I already have the sheets created by employee name. Mark "Stefi" wrote: 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: 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" wrote: 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: 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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| store formula in macro then paste it when macro runs | Excel Programming | |||
| formula or macro to change a formula in muliple spreadsheets | Excel Programming | |||
| use macro to copy formula with value and not formula without value | Excel Programming | |||
| Formula in macro causes macro to fail | Excel Programming | |||
| Formula expected end of statement error, typing formula into cell as part of VBA macro | Excel Programming | |||
Linear Mode
