using lookup to return more than one answer in date format
 

Hi. I did search the messages, and could not find what I was looking for.

I am using Excel as a rollbook for all my classes. The dates go across and
the names go down.
I have it adding up all the A's and T's so I can see the totals of how many
times Absent and Tardy by using CountIf.

What I want it to do is give me a summary of which days exactly they were
absent.

So here is what it looks like...
Row 1 is the headers and dates
Row 2 column G is their names... From H3:DD26, I have the grids where I type
in an A or a T.
Right now in columns A:F, I have it adding up how many A's and T's they have
for each quarter, and totals.

What I want it to do is to give me a list of the dates they were absent
(marked by an A). When I try the lookup, it only gives me the first one it
finds. I want all of the dates they were absent to show up.

I figured I'd have to put this below the current range (in row 28 or below).
And, I'd like the dates to go across the row and tell me what the date of
their A's are.

Make sense?? Reply if you need more info!

using lookup to return more than one answer in date format
 

Geni,

The easiest way is to copy your entire table, and go to another sheet and use pastespecial /
transpose. Then select the new table, and use data filters to show just the A's and/or T's for the
student of interest. The dates where there are not A's or T's will be hidden when you filter.

HTH,
Bernie
MS Excel MVP


"Geni Slaughter" wrote in message
...
Hi. I did search the messages, and could not find what I was looking for.

I am using Excel as a rollbook for all my classes. The dates go across and
the names go down.
I have it adding up all the A's and T's so I can see the totals of how many
times Absent and Tardy by using CountIf.

What I want it to do is give me a summary of which days exactly they were
absent.

So here is what it looks like...
Row 1 is the headers and dates
Row 2 column G is their names... From H3:DD26, I have the grids where I type
in an A or a T.
Right now in columns A:F, I have it adding up how many A's and T's they have
for each quarter, and totals.

What I want it to do is to give me a list of the dates they were absent
(marked by an A). When I try the lookup, it only gives me the first one it
finds. I want all of the dates they were absent to show up.

I figured I'd have to put this below the current range (in row 28 or below).
And, I'd like the dates to go across the row and tell me what the date of
their A's are.

Make sense?? Reply if you need more info!

using lookup to return more than one answer in date format
 

Geni,

Here is one way.

Replicate the names in column G, below the roll-call table, say starting at
row 100. Select H100:DD100, then in the formula bar (not the cell) add this
formula

=IF(ISERROR(SMALL(IF(H3:DD3="A",COLUMN(H3:DD3),"") ,COLUMN($H$1:$DD$1)-COLUMN
($H$1)+1)),"",
INDEX($H$1:$DD$1,SMALL(IF(H3:DD3="A",COLUMN($H$1:$ DD$1)-COLUMN($H$1)+1,""),C
OLUMN($H$1:$DD$1)-COLUMN($H$1)+1)))

which is an array formula, so commit with Ctrl-Shift-Enter.

Then just copy down for each pupil.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Geni Slaughter" wrote in message
...
Hi. I did search the messages, and could not find what I was looking for.

I am using Excel as a rollbook for all my classes. The dates go across
and
the names go down.
I have it adding up all the A's and T's so I can see the totals of how
many
times Absent and Tardy by using CountIf.

What I want it to do is give me a summary of which days exactly they were
absent.

So here is what it looks like...
Row 1 is the headers and dates
Row 2 column G is their names... From H3:DD26, I have the grids where I
type
in an A or a T.
Right now in columns A:F, I have it adding up how many A's and T's they
have
for each quarter, and totals.

What I want it to do is to give me a list of the dates they were absent
(marked by an A). When I try the lookup, it only gives me the first one
it
finds. I want all of the dates they were absent to show up.

I figured I'd have to put this below the current range (in row 28 or
below).
And, I'd like the dates to go across the row and tell me what the date
of
their A's are.

Make sense?? Reply if you need more info!

using lookup to return more than one answer in date format
 

A little out of the box might be to use a pivot table?
HTH,
--
Gary Brown

If this post was helpful, please click the ''Yes'' button next to ''Was this
Post Helpfull to you?''.


"Geni Slaughter" wrote:

Hi. I did search the messages, and could not find what I was looking for.

I am using Excel as a rollbook for all my classes. The dates go across and
the names go down.
I have it adding up all the A's and T's so I can see the totals of how many
times Absent and Tardy by using CountIf.

What I want it to do is give me a summary of which days exactly they were
absent.

So here is what it looks like...
Row 1 is the headers and dates
Row 2 column G is their names... From H3:DD26, I have the grids where I type
in an A or a T.
Right now in columns A:F, I have it adding up how many A's and T's they have
for each quarter, and totals.

What I want it to do is to give me a list of the dates they were absent
(marked by an A). When I try the lookup, it only gives me the first one it
finds. I want all of the dates they were absent to show up.

I figured I'd have to put this below the current range (in row 28 or below).
And, I'd like the dates to go across the row and tell me what the date of
their A's are.

Make sense?? Reply if you need more info!

using lookup to return more than one answer in date format
 

Thank you for your help!!!
The first 2 worked, and I did not try a pivot table.
Thank you very much. Makes my absence lookup much easier when I'm telling
kids when they were absent. You know how it goes at the end of the term.
Kids suddenly get concerned with their absences & grades HA HA.

"Geni Slaughter" wrote:

Hi. I did search the messages, and could not find what I was looking for.

I am using Excel as a rollbook for all my classes. The dates go across and
the names go down.
I have it adding up all the A's and T's so I can see the totals of how many
times Absent and Tardy by using CountIf.

What I want it to do is give me a summary of which days exactly they were
absent.

So here is what it looks like...
Row 1 is the headers and dates
Row 2 column G is their names... From H3:DD26, I have the grids where I type
in an A or a T.
Right now in columns A:F, I have it adding up how many A's and T's they have
for each quarter, and totals.

What I want it to do is to give me a list of the dates they were absent
(marked by an A). When I try the lookup, it only gives me the first one it
finds. I want all of the dates they were absent to show up.

I figured I'd have to put this below the current range (in row 28 or below).
And, I'd like the dates to go across the row and tell me what the date of
their A's are.

Make sense?? Reply if you need more info!