Pulling details of failing students into one sheet.

[rrlowery]

Hello everyone,

Let me give you a bit of background, and hopefully someone will be able to help me.

I work in a school, and when the students complete their SATS/Exam papers the information is fed into a spreadsheet for that class. i.e. Class 1 with 30 students will have a class1.xls file, Class 2 will have class2.xls

There are 3 worksheets per file. Test A, Test B, Mental. (Students complete either Test A or Test B, and the Mental Test)

These worksheets are laid out with..

• Generic information in cells A1:E15 (Class Name, Teacher, Number in class)
• Student names in columns, starting at G16.
• Gender (M or F) starting in G17
• Question information in A18:E55 (i.e. Q1 Handling Data Use Bar Chart)
• Student marks per answer in G20:AO55 (i.e. 0, 1, 2, etc)
• Marks for others papers (Test A or B + Mental) in G57:AO62 inc total marks for all papers.
• Complicated formula to work out grade/level in G63:AO63

Hopefully that is enough background.

Anyway the teachers want to pull all the students who are failing (total marks less than "x") and put them into their own separate workbook.

I've been looking at HLOOKUP, and VLOOKUP but not sure how to go about it.

Basically I need a formula that does something like...

• Look at Classxx.xls, Test x, Row X, Columns X-Y
• If the total marks is below XX return all data from the same column.

I need it to do this with all the tests and all the workbooks.

I have a blank form if anyone would like to see it.

Is it possible?
Is it simple... ish...

I use spreadsheets a lot, but this is the first time i've tried something this difficult.

Thanks in advance.

[dilipandey]

Hi rrlowery

welcome to the forum.

Suggest you to upload a small sample workbook along with your expected results. thanks.


Regards,
DILIPandey
<click on below * if this helps>

[abubaniyan]

hey dude mail me this sample sheet.....wll help you....my e-mail is abybaniyan@gmail.com