Ask Your Question
0

Search within another sheet [closed]

asked 2014-11-07 09:30:15 +0200

AussieGuy gravatar image

I am trying to collate student marks. Because of the different systems, all the lists are different, so I can't just copy and paste between sheets. However, there is one sheet ("MARKS") which contains the correct current list. What I need to do is to copy the marks from another sheet to this one.

Something like this:

  1. For each student, the first cell in that row will be an ID number; search in the next sheet ("Other") for that ID number.
  2. Copy the value from the last cell in that row in "Other" to the relevent cell in the current row of "MARKS".

For example, suppose I'm in row 49, and cell A49 of sheet "MARKS" contains "12345678". Seaching in sheet "Other" finds 12345678 in row 53. So I need to copy K53 to E49.

Is there an easy way to do this?

edit retag flag offensive reopen merge delete

Closed for the following reason the question is answered, right answer was accepted by Alex Kemp
close date 2016-03-05 22:11:22.016969

1 Answer

Sort by » oldest newest most voted
0

answered 2014-11-07 13:41:10 +0200

Lupp gravatar image

updated 2014-11-07 13:43:32 +0200

"What I need to do is to copy the marks from another sheet to this one." I assume "the other sheet" is '$Other' and "this one" is the sheet '$MARKS'.

You have to define and pass the ranges for looking up the foreign keys and getting the relevant field(s). If the IDs in 'Other' are, say, in $Other.$A$2:$A$20001 and the relevant marks in $Other.$K$2:$K$20001 you might use:

=INDEX($Other.$K$2:$K$20001;MATCH($A49;$Other.$A$2:$A$20001;0))

in the cell, say, K49 of 'Marks' to poll the vallue you want.

This is rather raw. I would prefer to keep or prepare a bit of meta info and then use OFFSET() to create and pass range references.

For further advice I need to know more details. Best would be a reduced and depersonalised but relevant example file.

edit flag offensive delete link more

Question Tools

1 follower

Stats

Asked: 2014-11-07 09:30:15 +0200

Seen: 1,049 times

Last updated: Nov 07 '14