Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help with function, if possible
Hi all,
Using Excel 2003. I have 12 sheets, each labled with the month name, I use each sheet for a calendar for that month. Sheet looks similar to this, D1 formatted 6/1/08, D2,D3 =A1, E2,E3 =A1+1, formatted as d & ddd, formatted across til the month end. (this sheet name would be June) A B C D E F 1 June 08 2 1 2 3 3 Sun Mon Tue 4 Steve Off Off Desk What I have done, is created another sheet, called "Task", A1 would equal the employee name, A2 would equal the date (of the task to be preformed), A3 would be the 2 digit code for the task for that given date. What function would I use that would match the sheet based on the date from the "Task" sheet with the calendar sheet name of that month entered, match the name, and put the 2 digit code for the correct name in the matching date box monthly sheet. Thanks in advance for any help you can offer. Steve D. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help with function, if possible
It is not clear whether you want data from monthly sheet into Task sheet or
the other way? Give an example row from a monthly sheet and Task sheet. "Steve D" wrote: Hi all, Using Excel 2003. I have 12 sheets, each labled with the month name, I use each sheet for a calendar for that month. Sheet looks similar to this, D1 formatted 6/1/08, D2,D3 =A1, E2,E3 =A1+1, formatted as d & ddd, formatted across til the month end. (this sheet name would be June) A B C D E F 1 June 08 2 1 2 3 3 Sun Mon Tue 4 Steve Off Off Desk What I have done, is created another sheet, called "Task", A1 would equal the employee name, A2 would equal the date (of the task to be preformed), A3 would be the 2 digit code for the task for that given date. What function would I use that would match the sheet based on the date from the "Task" sheet with the calendar sheet name of that month entered, match the name, and put the 2 digit code for the correct name in the matching date box monthly sheet. Thanks in advance for any help you can offer. Steve D. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help with function, if possible
Try these formulas:
=INDIRECT(TEXT(NOW(),"mmm")&"!a1") 'this formula derives the current month and then accesses the sheet which is named as the current month...nov in this case. =INDIRECT("nov"&"!a1") 'Directly access sheet called nov and the cell a1. These are just small tips. Pls let me know if we are in the right track and these statements have helped you. -- Kind Regards, Satti Charvak Only an Excel Enthusiast "Sheeloo" wrote: It is not clear whether you want data from monthly sheet into Task sheet or the other way? Give an example row from a monthly sheet and Task sheet. "Steve D" wrote: Hi all, Using Excel 2003. I have 12 sheets, each labled with the month name, I use each sheet for a calendar for that month. Sheet looks similar to this, D1 formatted 6/1/08, D2,D3 =A1, E2,E3 =A1+1, formatted as d & ddd, formatted across til the month end. (this sheet name would be June) A B C D E F 1 June 08 2 1 2 3 3 Sun Mon Tue 4 Steve Off Off Desk What I have done, is created another sheet, called "Task", A1 would equal the employee name, A2 would equal the date (of the task to be preformed), A3 would be the 2 digit code for the task for that given date. What function would I use that would match the sheet based on the date from the "Task" sheet with the calendar sheet name of that month entered, match the name, and put the 2 digit code for the correct name in the matching date box monthly sheet. Thanks in advance for any help you can offer. Steve D. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help with function, if possible
Sorry for any confusion, trying to move data from the task sheet, into the
monthly sheet(s). Here is some on the monthly sheet for June. A B C D E F G 1 June 08 2 1 2 3 4 3 Sun Mon Tue Wed 4 Steve Off Off T8 T8 5 Mark V8 V8 6 Bill S8 S8 Off Off Here is some of the task sheet A B C D E F G 1 NAME DATE CODE 2 Steve 6/3/08 T8 3 Steve 6/4/08 T8 4 Mark 6/1/08 V8 5 Mark 6/2/08 V8 6 Bill 6/1/08 S8 7 Bill 6/2/08 S8 Trying to take data from the task sheet, put it in the correct monthly sheet, have it look for the correct name, then put the 2 digit code under the correct date column, on that correct name row on the monthly sheet. Not sure if possible, thanks for any help. Steve D "Sheeloo" wrote: It is not clear whether you want data from monthly sheet into Task sheet or the other way? Give an example row from a monthly sheet and Task sheet. "Steve D" wrote: Hi all, Using Excel 2003. I have 12 sheets, each labled with the month name, I use each sheet for a calendar for that month. Sheet looks similar to this, D1 formatted 6/1/08, D2,D3 =A1, E2,E3 =A1+1, formatted as d & ddd, formatted across til the month end. (this sheet name would be June) A B C D E F 1 June 08 2 1 2 3 3 Sun Mon Tue 4 Steve Off Off Desk What I have done, is created another sheet, called "Task", A1 would equal the employee name, A2 would equal the date (of the task to be preformed), A3 would be the 2 digit code for the task for that given date. What function would I use that would match the sheet based on the date from the "Task" sheet with the calendar sheet name of that month entered, match the name, and put the 2 digit code for the correct name in the matching date box monthly sheet. Thanks in advance for any help you can offer. Steve D. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help with function, if possible
I am assuming the simple solution where you have the workers' names
already in the monthly sheets starting from A4. If this is not the case and the employees might be different from month to month say so, for some more complex formulas. In 'June 08'!D4: =INDEX(Task!$C$2:$C$101,MATCH(1,(Task!$A$2:$A$101= $A4)*(Task!$B$2:$B $101=$D$1+D$2-1),0)) This is an *array* formula (commit with Shift+Ctrl+Enter). You can copy this down and across each sheet. HTH Kostis Vezerides On Nov 6, 4:11*pm, Steve D wrote: Sorry for any confusion, trying to move data from the task sheet, into the monthly sheet(s). Here is some on the monthly sheet for June. * * * *A * * * B * * * C * * * D * * * E * * * *F * * * G 1 * * * * * * * * * * * * * *June 08 2 * * * * * * * * * * * * * * * * 1 * * * 2 * * * 3 * * * *4 3 * * * * * * * * * * * * * * * Sun * *Mon * Tue * *Wed 4 * *Steve * * * * * * * * * *Off * *Off * * *T8 * * *T8 5 * *Mark * * * * * * * * * * *V8 * * V8 6 * *Bill * * * * * * * * * * * * S8 * * S8 * * Off * * Off Here is some of the task sheet * * * A * * * B * * * C * * * D * * * E * * * *F * * * G 1 *NAME *DATE *CODE * * * * * * * * * 2 *Steve * 6/3/08 *T8 3 *Steve * 6/4/08 *T8 4 *Mark * *6/1/08 *V8 5 *Mark * *6/2/08 *V8 6 *Bill * * * 6/1/08 *S8 7 *Bill * * * 6/2/08 *S8 Trying to take data from the task sheet, put it in the correct monthly sheet, have it look for the correct name, then put the 2 digit code under the correct date column, on that correct name row on the monthly sheet. Not sure if possible, thanks for any help. Steve D * * * * * * * * * * * "Sheeloo" wrote: It is not clear whether you want data from monthly sheet into Task sheet or the other way? Give an example row from a monthly sheet and Task sheet. "Steve D" wrote: Hi all, Using Excel 2003. I have 12 sheets, each labled with the month name, I use each sheet for a calendar for that month. Sheet looks similar to this, D1 formatted 6/1/08, D2,D3 =A1, E2,E3 =A1+1, formatted as d & ddd, formatted across til the month end. (this sheet name would be June) * * * *A * * * B * * * C * * * D * * * E * * * *F 1 * * * * * * * * * * * * * *June 08 2 * * * * * * * * * * * * * * * * 1 * * * 2 * * * 3 3 * * * * * * * * * * * * * * * Sun * *Mon * Tue 4 * *Steve * * * * * * * * * *Off * *Off * * Desk What I have done, is created another sheet, called "Task", A1 would equal the employee name, A2 would equal the date (of the task to be preformed), A3 would be the 2 digit code for the task for that given date. What function would I use that would match the sheet based on the date from the "Task" sheet with the calendar sheet name of that month entered, match the name, and put the 2 digit code for the correct name in the matching date box monthly sheet. Thanks in advance for any help you can offer. Steve D. |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help with function, if possible
Thank you for the formula. Yes, the names start in A4 on each sheet. I tried
the formula, entered it as an array, the cells come up with #N/A. When running evaluate formula, under evaluation, receive INDEX(Task!$C$2:$C$101,#N/A) Thanks in advance for any help. Steve D. "vezerid" wrote: I am assuming the simple solution where you have the workers' names already in the monthly sheets starting from A4. If this is not the case and the employees might be different from month to month say so, for some more complex formulas. In 'June 08'!D4: =INDEX(Task!$C$2:$C$101,MATCH(1,(Task!$A$2:$A$101= $A4)*(Task!$B$2:$B $101=$D$1+D$2-1),0)) This is an *array* formula (commit with Shift+Ctrl+Enter). You can copy this down and across each sheet. HTH Kostis Vezerides On Nov 6, 4:11 pm, Steve D wrote: Sorry for any confusion, trying to move data from the task sheet, into the monthly sheet(s). Here is some on the monthly sheet for June. A B C D E F G 1 June 08 2 1 2 3 4 3 Sun Mon Tue Wed 4 Steve Off Off T8 T8 5 Mark V8 V8 6 Bill S8 S8 Off Off Here is some of the task sheet A B C D E F G 1 NAME DATE CODE 2 Steve 6/3/08 T8 3 Steve 6/4/08 T8 4 Mark 6/1/08 V8 5 Mark 6/2/08 V8 6 Bill 6/1/08 S8 7 Bill 6/2/08 S8 Trying to take data from the task sheet, put it in the correct monthly sheet, have it look for the correct name, then put the 2 digit code under the correct date column, on that correct name row on the monthly sheet. Not sure if possible, thanks for any help. Steve D "Sheeloo" wrote: It is not clear whether you want data from monthly sheet into Task sheet or the other way? Give an example row from a monthly sheet and Task sheet. "Steve D" wrote: Hi all, Using Excel 2003. I have 12 sheets, each labled with the month name, I use each sheet for a calendar for that month. Sheet looks similar to this, D1 formatted 6/1/08, D2,D3 =A1, E2,E3 =A1+1, formatted as d & ddd, formatted across til the month end. (this sheet name would be June) A B C D E F 1 June 08 2 1 2 3 3 Sun Mon Tue 4 Steve Off Off Desk What I have done, is created another sheet, called "Task", A1 would equal the employee name, A2 would equal the date (of the task to be preformed), A3 would be the 2 digit code for the task for that given date. What function would I use that would match the sheet based on the date from the "Task" sheet with the calendar sheet name of that month entered, match the name, and put the 2 digit code for the correct name in the matching date box monthly sheet. Thanks in advance for any help you can offer. Steve D. |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help with function, if possible
We have to understand better why you get the #N/A. Use instead a
smaller range (e.g. A2:A11 and corresponding) and instead of using formula evaluation use the inline evaluation facility in the formula bar: Visit the cell that gives you N/A, then select in the formula bar the part (Task!$A$2:$A$101=$A4). Press F9 (as in recalculation) to see which array is produced. Does it have TRUE where expected? To be more detailed, select the part $B$2:$B$11 and press F9. Are these dates or text? In your result, #N/A means that there was no instance of finding a name equal to A4 and a date equal to D1+D2-1. Possible reasons for this: there are extra spaces in the names, dates are stored as text, dates have the format d/m/y and are misleading etc. These are all I can think of. And one more thing, have you done array-entering before? If you do it properly then your formula should be displayed inside {} in the formula bar. HTH Kostis On Nov 8, 5:48*pm, Steve D wrote: Thank you for the formula. Yes, the names start in A4 on each sheet. I tried the formula, entered it as an array, the cells come up with #N/A. When running evaluate formula, under evaluation, receive INDEX(Task!$C$2:$C$101,#N/A) Thanks in advance for any help. *Steve D. "vezerid" wrote: I am assuming the simple solution where you have the workers' names already in the monthly sheets starting from A4. If this is not the case and the employees might be different from month to month say so, for some more complex formulas. In 'June 08'!D4: =INDEX(Task!$C$2:$C$101,MATCH(1,(Task!$A$2:$A$101= $A4)*(Task!$B$2:$B $101=$D$1+D$2-1),0)) This is an *array* formula (commit with Shift+Ctrl+Enter). You can copy this down and across each sheet. HTH Kostis Vezerides On Nov 6, 4:11 pm, Steve D wrote: Sorry for any confusion, trying to move data from the task sheet, into the monthly sheet(s). Here is some on the monthly sheet for June. * * * *A * * * B * * * C * * * D * * * E * * * *F * * * G 1 * * * * * * * * * * * * * *June 08 2 * * * * * * * * * * * * * * * * 1 * * * 2 * * * 3 * * * *4 3 * * * * * * * * * * * * * * * Sun * *Mon * Tue * *Wed 4 * *Steve * * * * * * * * * *Off * *Off * * *T8 * * *T8 5 * *Mark * * * * * * * * * * *V8 * * V8 6 * *Bill * * * * * * * * * * * * S8 * * S8 * * Off * * Off Here is some of the task sheet * * * A * * * B * * * C * * * D * * * E * * * *F * * * G 1 *NAME *DATE *CODE * * * * * * * * * 2 *Steve * 6/3/08 *T8 3 *Steve * 6/4/08 *T8 4 *Mark * *6/1/08 *V8 5 *Mark * *6/2/08 *V8 6 *Bill * * * 6/1/08 *S8 7 *Bill * * * 6/2/08 *S8 Trying to take data from the task sheet, put it in the correct monthly sheet, have it look for the correct name, then put the 2 digit code under the correct date column, on that correct name row on the monthly sheet. Not sure if possible, thanks for any help. Steve D * * * * * * * * * * * "Sheeloo" wrote: It is not clear whether you want data from monthly sheet into Task sheet or the other way? Give an example row from a monthly sheet and Task sheet. "Steve D" wrote: Hi all, Using Excel 2003. I have 12 sheets, each labled with the month name, I use each sheet for a calendar for that month. Sheet looks similar to this, D1 formatted 6/1/08, D2,D3 =A1, E2,E3 =A1+1, formatted as d & ddd, formatted across til the month end. (this sheet name would be June) * * * *A * * * B * * * C * * * D * * * E * * * *F 1 * * * * * * * * * * * * * *June 08 2 * * * * * * * * * * * * * * * * 1 * * * 2 * * * 3 3 * * * * * * * * * * * * * * * Sun * *Mon * Tue 4 * *Steve * * * * * * * * * *Off * *Off * * Desk What I have done, is created another sheet, called "Task", A1 would equal the employee name, A2 would equal the date (of the task to be preformed), A3 would be the 2 digit code for the task for that given date. What function would I use that would match the sheet based on the date from the "Task" sheet with the calendar sheet name of that month entered, match the name, and put the 2 digit code for the correct name in the matching date box monthly sheet. Thanks in advance for any help you can offer. Steve D. |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help with function, if possible
Hi vezerid. Thanks for all the help, but I have come up with a formula that
works, not sure if it is the best, but it does what I need. Here is the code IF(ISERROR(INDEX(Task!C2:C6,MATCH(June!B6&June!D2, Task!A2:A6&Task!B2:B6,0))),"",INDEX(Task!C2:C6,MAT CH(June!B6&June!D2,Task!A2:A6&Task!B2:B6,0))). Thanks again for all the help. "vezerid" wrote: We have to understand better why you get the #N/A. Use instead a smaller range (e.g. A2:A11 and corresponding) and instead of using formula evaluation use the inline evaluation facility in the formula bar: Visit the cell that gives you N/A, then select in the formula bar the part (Task!$A$2:$A$101=$A4). Press F9 (as in recalculation) to see which array is produced. Does it have TRUE where expected? To be more detailed, select the part $B$2:$B$11 and press F9. Are these dates or text? In your result, #N/A means that there was no instance of finding a name equal to A4 and a date equal to D1+D2-1. Possible reasons for this: there are extra spaces in the names, dates are stored as text, dates have the format d/m/y and are misleading etc. These are all I can think of. And one more thing, have you done array-entering before? If you do it properly then your formula should be displayed inside {} in the formula bar. HTH Kostis On Nov 8, 5:48 pm, Steve D wrote: Thank you for the formula. Yes, the names start in A4 on each sheet. I tried the formula, entered it as an array, the cells come up with #N/A. When running evaluate formula, under evaluation, receive INDEX(Task!$C$2:$C$101,#N/A) Thanks in advance for any help. Steve D. "vezerid" wrote: I am assuming the simple solution where you have the workers' names already in the monthly sheets starting from A4. If this is not the case and the employees might be different from month to month say so, for some more complex formulas. In 'June 08'!D4: =INDEX(Task!$C$2:$C$101,MATCH(1,(Task!$A$2:$A$101= $A4)*(Task!$B$2:$B $101=$D$1+D$2-1),0)) This is an *array* formula (commit with Shift+Ctrl+Enter). You can copy this down and across each sheet. HTH Kostis Vezerides On Nov 6, 4:11 pm, Steve D wrote: Sorry for any confusion, trying to move data from the task sheet, into the monthly sheet(s). Here is some on the monthly sheet for June. A B C D E F G 1 June 08 2 1 2 3 4 3 Sun Mon Tue Wed 4 Steve Off Off T8 T8 5 Mark V8 V8 6 Bill S8 S8 Off Off Here is some of the task sheet A B C D E F G 1 NAME DATE CODE 2 Steve 6/3/08 T8 3 Steve 6/4/08 T8 4 Mark 6/1/08 V8 5 Mark 6/2/08 V8 6 Bill 6/1/08 S8 7 Bill 6/2/08 S8 Trying to take data from the task sheet, put it in the correct monthly sheet, have it look for the correct name, then put the 2 digit code under the correct date column, on that correct name row on the monthly sheet. Not sure if possible, thanks for any help. Steve D "Sheeloo" wrote: It is not clear whether you want data from monthly sheet into Task sheet or the other way? Give an example row from a monthly sheet and Task sheet. "Steve D" wrote: Hi all, Using Excel 2003. I have 12 sheets, each labled with the month name, I use each sheet for a calendar for that month. Sheet looks similar to this, D1 formatted 6/1/08, D2,D3 =A1, E2,E3 =A1+1, formatted as d & ddd, formatted across til the month end. (this sheet name would be June) A B C D E F 1 June 08 2 1 2 3 3 Sun Mon Tue 4 Steve Off Off Desk What I have done, is created another sheet, called "Task", A1 would equal the employee name, A2 would equal the date (of the task to be preformed), A3 would be the 2 digit code for the task for that given date. What function would I use that would match the sheet based on the date from the "Task" sheet with the calendar sheet name of that month entered, match the name, and put the 2 digit code for the correct name in the matching date box monthly sheet. Thanks in advance for any help you can offer. Steve D. |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help with function, if possible
Forgot to mention, that the formula is entered as an array (commit with
Shift+Ctrl+Enter). . "Steve D" wrote: Hi vezerid. Thanks for all the help, but I have come up with a formula that works, not sure if it is the best, but it does what I need. Here is the code IF(ISERROR(INDEX(Task!C2:C6,MATCH(June!B6&June!D2, Task!A2:A6&Task!B2:B6,0))),"",INDEX(Task!C2:C6,MAT CH(June!B6&June!D2,Task!A2:A6&Task!B2:B6,0))). Thanks again for all the help. "vezerid" wrote: We have to understand better why you get the #N/A. Use instead a smaller range (e.g. A2:A11 and corresponding) and instead of using formula evaluation use the inline evaluation facility in the formula bar: Visit the cell that gives you N/A, then select in the formula bar the part (Task!$A$2:$A$101=$A4). Press F9 (as in recalculation) to see which array is produced. Does it have TRUE where expected? To be more detailed, select the part $B$2:$B$11 and press F9. Are these dates or text? In your result, #N/A means that there was no instance of finding a name equal to A4 and a date equal to D1+D2-1. Possible reasons for this: there are extra spaces in the names, dates are stored as text, dates have the format d/m/y and are misleading etc. These are all I can think of. And one more thing, have you done array-entering before? If you do it properly then your formula should be displayed inside {} in the formula bar. HTH Kostis On Nov 8, 5:48 pm, Steve D wrote: Thank you for the formula. Yes, the names start in A4 on each sheet. I tried the formula, entered it as an array, the cells come up with #N/A. When running evaluate formula, under evaluation, receive INDEX(Task!$C$2:$C$101,#N/A) Thanks in advance for any help. Steve D. "vezerid" wrote: I am assuming the simple solution where you have the workers' names already in the monthly sheets starting from A4. If this is not the case and the employees might be different from month to month say so, for some more complex formulas. In 'June 08'!D4: =INDEX(Task!$C$2:$C$101,MATCH(1,(Task!$A$2:$A$101= $A4)*(Task!$B$2:$B $101=$D$1+D$2-1),0)) This is an *array* formula (commit with Shift+Ctrl+Enter). You can copy this down and across each sheet. HTH Kostis Vezerides On Nov 6, 4:11 pm, Steve D wrote: Sorry for any confusion, trying to move data from the task sheet, into the monthly sheet(s). Here is some on the monthly sheet for June. A B C D E F G 1 June 08 2 1 2 3 4 3 Sun Mon Tue Wed 4 Steve Off Off T8 T8 5 Mark V8 V8 6 Bill S8 S8 Off Off Here is some of the task sheet A B C D E F G 1 NAME DATE CODE 2 Steve 6/3/08 T8 3 Steve 6/4/08 T8 4 Mark 6/1/08 V8 5 Mark 6/2/08 V8 6 Bill 6/1/08 S8 7 Bill 6/2/08 S8 Trying to take data from the task sheet, put it in the correct monthly sheet, have it look for the correct name, then put the 2 digit code under the correct date column, on that correct name row on the monthly sheet. Not sure if possible, thanks for any help. Steve D "Sheeloo" wrote: It is not clear whether you want data from monthly sheet into Task sheet or the other way? Give an example row from a monthly sheet and Task sheet. "Steve D" wrote: Hi all, Using Excel 2003. I have 12 sheets, each labled with the month name, I use each sheet for a calendar for that month. Sheet looks similar to this, D1 formatted 6/1/08, D2,D3 =A1, E2,E3 =A1+1, formatted as d & ddd, formatted across til the month end. (this sheet name would be June) A B C D E F 1 June 08 2 1 2 3 3 Sun Mon Tue 4 Steve Off Off Desk What I have done, is created another sheet, called "Task", A1 would equal the employee name, A2 would equal the date (of the task to be preformed), A3 would be the 2 digit code for the task for that given date. What function would I use that would match the sheet based on the date from the "Task" sheet with the calendar sheet name of that month entered, match the name, and put the 2 digit code for the correct name in the matching date box monthly sheet. Thanks in advance for any help you can offer. Steve D. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
copy of excel file not showing formulal/function in the function b | Excel Discussion (Misc queries) | |||
LINKEDRANGE function - a complement to the PULL function (for getting values from a closed workbook) | Excel Worksheet Functions | |||
Offset function with nested match function not finding host ss. | Excel Worksheet Functions | |||
Emulate Index/Match combo function w/ VBA custom function | Excel Worksheet Functions | |||
Nested IF Function, Date Comparing, and NetworkDays Function | Excel Worksheet Functions |