Hello,

I am having a sheet with list of students and their active profile. I
enter the marks for each subject for a month. This happens for the next
month which is the next sheet. Suppose one student did not take the exam, he
comes inactive and his name should not appear in the next months sheet. What
formula should I use for this

Thank you

L Krishna Kumar

Assume student names are listed in Sheet1's col A from row2 down
with col B being the key col ("Marks") where any students absent will be
marked as: abs

In another Sheet2,
In A2:
=IF(Sheet1!B2="","",IF(Sheet1!B2<>"abs",ROW(),""))
Leave A1 blank

In B2:
=IF(ROWS($1:1)>COUNT(A:A),"",INDEX(Sheet1!A:A,SMALL(A:A,ROWS($1:1))))
Select A2:B2, copy down to cover the max expected extent of data in Sheet1.
Hide away col A. Col B will return the required list of students w/o those
marked as "abs" in Sheet1's col B, with all results neatly bunched at the top
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Krishna Kumar L" wrote:
> Hello,
>
> I am having a sheet with list of students and their active profile. I
> enter the marks for each subject for a month. This happens for the next
> month which is the next sheet. Suppose one student did not take the exam, he
> comes inactive and his name should not appear in the next months sheet. What
> formula should I use for this
>
> Thank you
>
> L Krishna Kumar
>

Max,

You have got me wrong. The sample sheet is mentioned herein
Sheet 1
Sl. Name Active Eng Math Geog

1 David yes 100 90 95
2 Roby yes 12 22 65
3 James no4 Steven no

Sheet 2Sl. Name Active Eng Math Geog

1 David yes 100 90 95
2 Roby yes 12 22 65

Sl. Name Active Eng Math Geog

1 David yes 100 90 95
2 Roby yes 12 22 65
3 James yesso if a person is not active in the first sheet he does not reflect in the
second sheet, also I will be adding new students in the middle of the year.
Try to in-cooperate that also.

Thank you

L Krishna Kumar




"Max" <(E-Mail Removed)> wrote in message
news:00230958-0939-405F-82F6-(E-Mail Removed)...
> Assume student names are listed in Sheet1's col A from row2 down
> with col B being the key col ("Marks") where any students absent will be
> marked as: abs
>
> In another Sheet2,
> In A2:
> =IF(Sheet1!B2="","",IF(Sheet1!B2<>"abs",ROW(),""))
> Leave A1 blank
>
> In B2:
> =IF(ROWS($1:1)>COUNT(A:A),"",INDEX(Sheet1!A:A,SMALL(A:A,ROWS($1:1))))
> Select A2:B2, copy down to cover the max expected extent of data in
> Sheet1.
> Hide away col A. Col B will return the required list of students w/o those
> marked as "abs" in Sheet1's col B, with all results neatly bunched at the
> top
> --
> Max
> Singapore
> http://savefile.com/projects/236895
> xdemechanik
> ---
> "Krishna Kumar L" wrote:
>> Hello,
>>
>> I am having a sheet with list of students and their active profile. I
>> enter the marks for each subject for a month. This happens for the next
>> month which is the next sheet. Suppose one student did not take the exam,
>> he
>> comes inactive and his name should not appear in the next months sheet.
>> What
>> formula should I use for this
>>
>> Thank you
>>
>> L Krishna Kumar
>>

> You have got me wrong.
Not really, think I was quite spot-on.
Just a matter of adapting it slightly to suit your actual set-up

Here's a sample implementation based on your set-up detail posted:
http://www.freefilehosting.net/download/3cb93
Auto update of sheets.xls

Source data in Sheet1, cols A to F, data from row2 down
with col C = key col ("Active") where: yes
marks the active students' lines to be brought over to Sheet2

In Sheet2,
In A2: =IF(Sheet1!C2="","",IF(Sheet1!C2="yes",ROW(),""))
Leave A1 blank. This is the criteria col.

In B2: =IF(C2="","",ROWS($1:1))
(this is for auto serial numbering)

In C2:
=IF(ROWS($1:1)>COUNT($A:$A),"",INDEX(Sheet1!C:C,SMALL($A:$A,ROWS($1:1))))
Copy C2 to G2. Select A2:G2, copy down to cover the max expected extent of
data in Sheet1. Minimize/hide away col A. Cols C to G will return the
required list of active students, with all results neatly bunched at the top.
Col B creates an auto serial numbering to top it off.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Krishna Kumar L" wrote:
> Max,
>
> You have got me wrong. The sample sheet is mentioned herein
> Sheet 1
> Sl. Name Active Eng Math Geog
>
> 1 David yes 100 90 95
> 2 Roby yes 12 22 65
> 3 James no4 Steven no
>
> Sheet 2Sl. Name Active Eng Math Geog
>
> 1 David yes 100 90 95
> 2 Roby yes 12 22 65
>
> Sl. Name Active Eng Math Geog
>
> 1 David yes 100 90 95
> 2 Roby yes 12 22 65
> 3 James yesso if a person is not active in the first sheet he does not reflect in the
> second sheet, also I will be adding new students in the middle of the year.
> Try to in-cooperate that also.
>
> Thank you
>
> L Krishna Kumar