Create dynamic list of candidates

[jb]

Hi Experts,

I have an excel file consisting of many subject-wise sheets having marks of students of different class.

Each sheet is given name based on subject. Each sheet may have different number of students but format of each sheet is same.

First column is roll number and second column is marks.

I want to prepare a dynamic list of students on a separate excel sheet where only those students should appear who were Absent or Cancelled in particular subject.

I am attaching sample file with it. I have just identified such students with conditional formatting.

Such list is to be prepared for all subject sheets.

for e.g.
RDBMS-I
2 AB
33 AB
40 AB

 

[xlstime]

Hi Jb,

Please see if this is ok

Hi Experts,

I have an excel file consisting of many subject-wise sheets having marks of students of different class.

Each sheet is given name based on subject. Each sheet may have different number of students but format of each sheet is same.

First column is roll number and second column is marks.

I want to prepare a dynamic list of students on a separate excel sheet where only those students should appear who were Absent or Cancelled in particular subject.

I am attaching sample file with it. I have just identified such students with conditional formatting.

Such list is to be prepared for all subject sheets.

for e.g.
RDBMS-I
2 AB
33 AB
40 AB

 

[jb]

Hi Jb,

Please see if this is ok

Hi xlstime,

Your solution is perfect. Thanks a lot.

 

[xlstime]

you'r welcome!..

Hi xlstime,

Your solution is perfect. Thanks a lot.

 

[jb]

you'r welcome!..

With your solution, now I am trying to make this sheet dynamic.

1.

Name of worksheet used in formula is fixed and when subject changed, editing is to be done in formula. e.g. MARK-RDBMSI, MARK-ADFS, MARK-SPM, MARK-IT etc. In non-submitted worksheet, title of subject is written. e.g. RDBMSI, ADFS, SPM, IT etc. so is it possible the concatenation of fixed part "MARK-" and subject title to be used in formula? so that we need not require to edit formula?

2.

Number of students are also different for different subjects. So if I type number of students alongwith subject title e.g. 49 and data is available on different sheets from row 5. so if number of candidates are 49 then range will be row 5 to row 53 i.e 49+4. f number of candidates are 8 then range will be row 5 to row 12 i.e 8+4.

I want to use this value in formula wherever fixed 53 value is referred which is required to be edited everytime if number of candidated changed. Is it possible?

Note: I do not want my end user to type 53 or 12 as number of candidates. They will type actual number of candidates.

 

[rahulshewale1]

hii,

Please find the attached solution with Advance filter.