Numbers formulas across multiple sheets

Question

Level 1

4 points

Numbers formulas across multiple sheets

I've got a spreadsheet with logins for a whole teaching group (example on pic 1), but would like to move individual student sheets, so they can stick them into their books. I'd like to use a layout like pic 2. I'd like to put in a formula that will allow me to switch between details by just changing the surname if that makes sense?

Could anybody help with the formula for this please? I tried VLookup but I don't really understand what I'm doing, so it keeps coming up with error messages... Any help is much appreciated! Thanks :)

iMac, OS X 10.11

Posted on Aug 16, 2020 10:39 AM

Reply

Answer 1

Top-ranking reply

Badunit

Level 7

28,216 points

Aug 16, 2020 3:42 PM in response to fraub1

That makes it easy.

EDIT: If you read this before I realized your first table has two header rows vs mine with one, my formulas were a little off. I also took away the blank row at the bottom of my table that I had included for expansion, to avoid any other inconsistencies. Below are the corrected formulas:

Formula in cell B2 of the Student Login table is:

=VLOOKUP($A$2,Class Logins::$3:$8,2,0)

You shouldn't have to include the sheet name if the Class Logins table is uniquely named in your document. If you do have to include the sheet name then it would be:

=VLOOKUP($A$2,Class Logins::Class Logins::$3:$8,2,0)

Copy/Paste the formula from cell B2 to cells D2,E2,D3,E3

In those other cells, change the second to last number in the formula to correspond with the column you want the data from. Username for Kahoot is coming from column 3, for instance so it would be

==VLOOKUP($A$2,Class Logins::$$3:$8,3,0)

Reply

Answer 2

Badunit

Level 7

28,216 points

Aug 16, 2020 12:17 PM in response to fraub1

I think the chance that two or more people will have the same surname is too high to use only the surname for your search. I recommend at least surname and first name, though even then there is a chance of two people with the same name. Or are you expecting when you put in the surname that all matching students will appear on the list, not just one? The answer to this question makes a difference to solving your problem.

Reply