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.

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" wrote in message
...
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.

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." wrote:

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" wrote in message
...
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.

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
"Preschool Mike" wrote in message
...
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." wrote:

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" wrote in
message
...
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.

You can email it to me at

--
Thanks,

Ronald R. Dodge, Jr.
Production Statistician
Master MOUS 2000
"Preschool Mike" wrote in message
...
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." wrote:

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" wrote in
message
...
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.

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

Thanks again,

Mike

"Ronald R. Dodge, Jr." wrote:

You can email it to me at

--
Thanks,

Ronald R. Dodge, Jr.
Production Statistician
Master MOUS 2000
"Preschool Mike" wrote in message
...
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." wrote:

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" wrote in
message
...
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.