VLookUp

[shoa]

Hello all



I have many worksheets, each worksheet has student ID, marks for each parts
of assignment and total mark.



For example:



Worksheet 1: Student ID: 232, part one: 4, part 2: 6, part 3..total
mark is 10

Worksheet 2 Student ID: 234, part one: 5, part 2: 7, part 3..
total mark is 12

Worksheet 3 Student ID: 231, part one: 10, part 2: 6, part 3. total
mark is 16



The student ID is always on cell A2 and total mark is always on cell C10
(for example). Other marks are in other cells.



Now I have one worksheet called "summary worksheet". In this worksheet I
have a column of ALL student IDs. What I want is that how I can use a
function to insert "total mark" for each student from the detail worksheets
above in to this "summary worksheet".

For example:



ID total mark

232 10

234 12

231 16



Is there any function to do that, could you please help

Many thanks

S.Hoa

 

[DDM]

shoa, please note that you received two responses to your earlier post. You
really should follow up that thread, rather than start a new one. In any
case, I think Dave Peterson is correct to point out that you do not need a
VLOOKUP. Rather, you simply need to refer to the cell on the other worksheet
from the cell on this one. Using your example below, it would go like this:

On your "Summary Sheet":

=Sheet1!A2 (result: 232)
=Sheet1!C10 (result: 10)
=Sheet2!A2 (result: 234)
=Sheet2!C10 (result: 12)

and so on.

 

[shoa]

Thank you

However, in the summary worksheet I have already a column of IDs. What I
really want is if there is a function that allow me to select "total mark"
of each student in invidual worksheet and "lookup" the ID (between
individual worksheet and summary worksheet) and put them in the column of
"total mark" in "summary worksheet".

 

[Arvi Laanemets]

Hi

There were others, who did answer directly your question. But I have some
advice about your database design in general. With your current design, you
are assigned to redesign your workbook every time yo have to add or remove
some student from database. For my taste it'll be annoying.

I don't belive there is so much info about every student, that you need a
whole worksheet for everyone of them. I think it'll be better to dissociate
data input and viewing of data. So think about design like this:

Create a sheet p.e. Students, with all info about student on single row
Students: StudentID, ..., Part1, Part2, Part3, Total, ...
and enter all info for all students into this table.

Create a sheet p.e. StudentReport
Select a cell, p.e. A1 on StudentReport, and then use Data.Validation.List
with source
=Students
Now when you select the cell A1, a dropdown marker is displayed right to it,
where you can select a student.

Rest of information about selected student you'll get using VLOOKUP. P.e.
when Part1 values are in column Students!B (Second column of table
StudentTable), then on StudentReport you get it using the formula
=VLOOKUP(A1,StudentTable,2,0)
etc., until all needed info is displayed.


Create a named ranges (Insert.Name.Define) p.e. Students and StudentTable
Students=OFFSET(Students!$A$2,,,COUNTIF(Students!$A:$A),1)
StudentTable=OFFSET(Students!$A$2,,,COUNTIF(Students!$A:$A),x)
where x is overall number of columns in your table on sheet Students

As I understod, you need a list of all students with their Total values. You
can get this directly from StudentsSheet, hidding all abundant columns, but
it'll be clumsy, and maybe you want use different srifts, or borders etc. So
better create another report sheet - StudentsSummary

Into cell A1 enter the formula
=IF(Students!A1="","",Students!A1)
Into Cell B1 enter the formula (I assume here the column Total is in
Students!D
=IF(Students!A1="","",Students!D1)
and now copy both formulas down at last for as much rows as there is them in
table Students (but better you have at least some spare rows ready, for case
you add some new students.

You can have any number of different report sheets - for single student, or
listing all of them, or displaying some summarized data (p.e. average
points, or max/min age, or number of male/female students - even grouped by
some parameter. Any info which you have put into Students table before, or
which you can calculate from there.

 

[Arvi Laanemets]

Sorry, but somehow all paragraphs in my answer got messed up. Here is it in
right order

Hi

There were others, who did answer directly your question. But I have some
advice about your database design in general. With your current design, you
are assigned to redesign your workbook every time yo have to add or remove
some student from database. For my taste it'll be annoying.

I don't belive there is so much info about every student, that you need a
whole worksheet for everyone of them. I think it'll be better to dissociate
data input and viewing of data. So think about design like this:

Create a sheet p.e. Students, with all info about student on single row
Students: StudentID, ..., Part1, Part2, Part3, Total, ...
and enter all info for all students into this table.

Create a named ranges (Insert.Name.Define) p.e. Students and StudentTable
Students=OFFSET(Students!$A$2,,,COUNTIF(Students!$A:$A),1)
StudentTable=OFFSET(Students!$A$2,,,COUNTIF(Students!$A:$A),x)
where x is overall number of columns in your table on sheet Students

Create a sheet p.e. StudentReport
Select a cell, p.e. A1 on StudentReport, and then use Data.Validation.List
with source
=Students
Now when you select the cell A1, a dropdown marker is displayed right to it,
where you can select a student.

Rest of information about selected student you'll get using VLOOKUP. P.e.
when Part1 values are in column Students!B (Second column of table
StudentTable), then on StudentReport you get it using the formula
=VLOOKUP(A1,StudentTable,2,0)
etc., until all needed info is displayed.

As I understod, you need a list of all students with their Total values. You
can get this directly from StudentsSheet, hidding all abundant columns, but
it'll be clumsy, and maybe you want use different srifts, or borders etc. So
better create another report sheet - StudentsSummary

Into cell A1 enter the formula
=IF(Students!A1="","",Students!A1)
Into Cell B1 enter the formula (I assume here the column Total is in
Students!D
=IF(Students!A1="","",Students!D1)
and now copy both formulas down at last for as much rows as there is them in
table Students (but better you have at least some spare rows ready, for case
you add some new students.

You can have any number of different report sheets - for single student, or
listing all of them, or displaying some summarized data (p.e. average
points, or max/min age, or number of male/female students - even grouped by
some parameter. Any info which you have put into Students table before, or
which you can calculate from there.

 

[shoa]

Thank you, Arvi

You are right, I need a good design and you have much help.

The reason for create each worksheet for each student is that I want to
print out details of what students have done for their assignments. That
mean in each sheet, there are cells for ID, name, comment for each part they
have done (as there are 10 parts in the assignment), total for each part.
Then I print that information as marking schema for each student. The reason
I do this is that I do not have to calculate by hand total mark for each
student as I can use the Sum function.

It can be seen that I can not use a row for a student.

The question here is: I have a separated sheet in that I have student IDs
and name. Now I want to insert back the total mark from invidual sheet of
each student to this sheet (based on the similar of ID). I do not know if
there is function to do that

Do you have any idea.
Thank you.

 

[Arvi Laanemets]

Hi

Thank you, Arvi

You are right, I need a good design and you have much help.

The reason for create each worksheet for each student is that I want to
print out details of what students have done for their assignments. That
mean in each sheet, there are cells for ID, name, comment for each part they
have done (as there are 10 parts in the assignment), total for each part.
Then I print that information as marking schema for each student. The reason
I do this is that I do not have to calculate by hand total mark for each
student as I can use the Sum function.

It can be seen that I can not use a row for a student.

It depends on your data. You sayd there are 10 parts. When you have for
every part only PartID, PartName, Comment and PartTotal, and all this on
single line, then you'll need in Students table only 2 columns for every
part (PartID is for both columns same, and PartName you can get into reports
from an additional Parts sheet (which contains columns PartID, PartName,
....). 2*10=20 columns. And you can have 256 columns on a sheet!

It'll be different, when you need to enter the info for every student
row-wise. P.e. for every part there can be several entries (at some date
some work is done, at next date some additional work, ets, until it's
finished . Or parts for different students are different. When this is the
case, then you need an additional sheet, p.e. Details like this
DetailID, StudentID, ...

How to get detailed info into report, you can get some ideas from my answer
to thread 'How to get selective cells' from Juco, March 04. 2004. 10:46, in
same NG here.