Creating a Student's Report

[Sumaia]

Hi Everybody,

I am working on student's marksheets and my excel spreadsheet is divided into two spread sheets. The first one contains a list of students and their marks and in the second one I'd like to create a report which will give me a summary of student's results and other details like college, group and level. By filling in his/her name in the name cell, I want all his/her details to appear in the report. What is the suitable function for such a case?

Looking forward to hearing from you.

Sumaia

 

[VoG]

If your first sheet contains their details as well as their marks then try VLOOKUP Excel VLookup Function Examples

 

[dmt32]

Hi welcome to the board.
As a suggestion, have a look here Templates for Education - Teachers & Students
They have free templates that you can download some of which may help with ideas for your project.
If no joy, post back as sure others will have suggestions.


Dave

 

[Sumaia]

Thanks Dave.

I will have a look. good suggestion.

 

[Akashwani]

Hi Sumaia and welcome to MrExcel.

The following is an example of Vlookup which VoG refers to......

Excel Workbook
	A	B	C	D	E	F	G
1	ID Number	Name	Amount	Animal	Transport	Date
2	AA10000	Bart Simpson	368	cat	Bike	01/01/2011
3	AA10001	Lisa Simpson	277	dog	Helicopter	02/01/2011
4	AA10002	Grandpa Simpson	474	Horse	Bus	03/01/2011
5	AA10003	Marge Simpson	217	fish	Tram	01/01/2011
6	AA10004	Santas Little Helper Simpson	330	fish	Helicopter	05/01/2011
7	AA10005	Maggie Simpson	346	cat	Plane	01/01/2011
8	AA10006	Milhouse Van Houten	472	Panda	Tut Tut	07/01/2011
9	AA10007	Ned Flanders	343	Tiger	Coach	08/01/2011
10	AA10008	Otto Mann	300	Lion	Carriage	09/01/2011
11	AA10009	Rod Flanders	343	Tiger	Bus	10/01/2011
12	AA10010	Moe Szyslak	147	dog	Car	01/01/2011
13	AA10011	Homer Simpson	455	fish	Tut Tut	09/01/2011
14	AA10012	Tod Flanders	143	Lion	trike	02/01/2011
15	AA10013	Fat Tony	176	Bull	Bike	03/01/2011
16	AA10014	Barney Gumble	485	cow	Helicopter	04/01/2011
17
18
19	ID Number	Name	Amount	Animal	Transport	Date
20	AA10011	Homer Simpson	455	fish	Tut Tut	09/01/2011
21
22	AA10005	Maggie Simpson	346	cat	Plane	01/01/2011
23
Sheet20

The results in the lower table A19:F22 can be on your second sheet.

The formula(s) on the first row (20) is looking up the name (Yellow) in B20.
The formula in C20 needs to be copied across.
The formula in A20 is used as Vlookup looks from Left to Right, where Index/Match can look both ways.

The formula in B22 is looking up the ID number (Blue) in A22, this can formula can be copied across.

I hope this helps to explain Vlookup to you, IF, you decided that you wanted to lookup a value that returns Multiple answers, then Vlookup will NOT do this for you, a rather complicated formula will be required.

You may want to take a look here.....

excelisfun -- Excel How To Videos - YouTube

Mike has the most amazing videos on Excel from Beginner level to Advanced.

Good luck with your project.

Ak

 

[Sumaia]

Thank you very much Akashwani. Your reply is really useful and informative. Grateful to you.

 

[Akashwani]

Hi Sumaia,

I'm pleased you found it helpful and thanks for the feedback.

Ak