Help with function, if possible

[Steve D]

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.

 

[Sheeloo]

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.

 

[Satti Charvak]

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.

 

[Steve D]

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

 

[vezerid]

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

 

[Steve D]

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]

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

 

[Steve D]

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,MATCH(June!B6&June!D2,Task!A2:A6&Task!B2:B6,0))).
Thanks again for all the help.

 

[Steve D]

Forgot to mention, that the formula is entered as an array (commit with
Shift+Ctrl+Enter). .