What's the formula?

[Rotata]

Excel 2003 on XP

Okay, here is the scenario.

I am trying to create a workbook whereby text meeting a certain criteria is
transfered from each of several worksheets onto a main worksheet.

Example:
On crew "alpha", which consist of 12 employees, John J is the only one
absent. The crew leader puts a 1 next to his name in the absent column.
On crew "beta", which consist of 10 employees, Mary B and Maria C are
absent. The
crew leader puts a 1 next to each individual in the absent column.

How do I get those three names to show up in list format on Page 1?

John J
Mary B
Maria C

I used this formula on sheet one. ("N" is the absent column and "A" is the
name)

=IF(alpha!N5:N25,">0","alpha!a5:a25")
Not only does this NOT work, but doesn't account for subsequent crews.

What is the correct way to do this?
--

 

[Shane Devenshire]

Hi,

Just a quick comment

=IF(alpha!N5:N25,">0","alpha!a5:a25")

Even assuming it did work what would you want it to do count, sum, average?

For sum you could use

=SUMIF(alpha!N5:N25,">0",alpha!a5:a25)

Note also ">0" is not legal in an IF statement try something like

=IF(alpha!N5:N25>0,alpha!a5:a25,"")

or look at

=SUMPRODUCT(--(alpha!N5:N25>0),alpha!a5:a25)

Also note that "alpha!a5:a25" would be considered a string (text) not a
range, don't quote ranges except in INDIRECT.

If this helps, please click the Yes button

Cheers,
Shane Devenshire

 

[Rotata]

Shane,

Thanks for the interest in my problem.

The Number of people absent from each crew sums to the Summary page quite
well. On the crew page, using an "If" statement, I have been able to show the
names of the absent people at the end of each row.

I want to automatically list the names of the absent employees from each of
the work crews onto the main Summary page.

I have never used INDIRECT, but am trying it out.