Formula for attendance record

[Preschool Mike]

On sheet one of my workbook I have my daily attendance record (e.g., date and
names of children absent) on sheet two of the same workbook I have my yearly
attendance record. I want to be able to come up with a formula so when I
enter the child's name on the daily attendance record it automatically
transfers the informations (A - meaning they are absent) to the yearly
attenance record in the appropriate cell for that date. Sample: Sheet 1 -
Cell A1=date; cells B1:B5 is where I enter the names of the children who are
absent. Sheet 2 - cell A5 where the information (A for absent) needs to go).
The formula needs to take into consideration the date and the students name
and return an "A" if both are true to cell A5 on sheet 2.

I hope that's clear enough. Easier to show you than explain.

 

[Ronald R. Dodge, Jr.]

Assuming you have the following format:

Sheet 1:

Column A = Date of Absence
Column B = Names of students absent for the date listed in column A

Note, for this to work, every row that has a student name in Column B must
also have a date in column A

Setup Column C as in Index field with the following formula

Example with Cell C5

=A5&"|"&B5

Student Names in Column B must be typed exactly how it will show up on Sheet
2 in Column A

Sheet 2:

Column A starting at Row 2 is the list of student names.

Row 1 starting in Column B and on to the right contains the dates.

In Cell B2, type in the following formula:

=IF(ISERROR(MATCH(B$1,&"|"&$A2,Sheet1!$C:$C,0),"","A")

Note the placement of the dollar signs as they are put in their respective
places to mark certain aspects as absolute while other aspects of the
formula doesn't have dollar signs to mark those aspects as relative. This
makes it easier for when copying and pasting or when filling down and right.

For me, once I type in the formula in B2, I can then select the entire area
that will have that basic formula and then press Ctrl-D (Fill Down) and also
press Ctrl-R (Fill Right).

The only thing about this, given all of the looking up that is taking place,
it may take a significant longer amount of time to calculate depending on
the system's capacity, how many different students, how many records of
absences there are, and how many dates there are. Also, if not using Excel
2007, you only have 256 columns to work with (Columns A though IV).

--
Thanks,

Ronald R. Dodge, Jr.
Production Statistician
Master MOUS 2000

 

[Preschool Mike]

I appreciate the help, but unfortunately that's not the format or setup I'm
using. I have little knowledge about writing formulas so please bear with
me. If it were possible I would type my formula in this format.

If cell E1 ("the child's name") on sheet 2 matches any cell from B46 thru
I52 ("which could be any child including said child") on sheet 1 and cell P7
(which is the date) on sheet 2 matches H44 (which is the date) on sheet 1
then cell P8 (on sheet 2) = A (for absent).

Note: If there is not a match then nothing should happen in cell P8. Also
the both the names and the dates must match to return an "A" for absent. I
can send you an email of my layout if I haven't been clear enough.

I hope that's a little clearer and I haven't made it more confusing.

Thanks,

Mike

 

[Ronald R. Dodge, Jr.]

Would be easier for me to work on the file itself as by having to look in
more than one column and row for a particular value, made it that much more
difficult formula wise, which then would probably resort to using User
Defined Function (UDF) within VBA to address the issue.

--
Thanks,

Ronald R. Dodge, Jr.
Production Statistician
Master MOUS 2000

 

[Ronald R. Dodge, Jr.]

You can email it to me at (e-mail address removed)

--
Thanks,

Ronald R. Dodge, Jr.
Production Statistician
Master MOUS 2000

 

[Preschool Mike]

Thanks for your help. I finally got the gist of your formula and configured
it to meet my needs.

Thanks again,

Mike