How to retrieve entire row (actually 26 columns) of data ?

[Guest]

I want my students to be able to see their grades online w/o viewing the
entire spreadsheet. My intention is to hide the working spreadsheet, & show
them a sheet with the same column headings (quiz 1, quiz 2, etc.). The data
cells will populate w/ the data from the underlying hidden sheet when they
type in their (unique) student ID number.

I'm comortable w/ vlookup, but haven't used the other possibilities, like
index/match, etc., and now I need a way to find the student ID in the
worksheet, & return all the data in that row.

 

[JE McGimpsey]

If your students have access to the XL workbook, you should assume that
they have access to every part of it. XL's internal protection is useful
only for preventing inadvertent mistakes:

http://www.mcgimpsey.com/excel/removepwords.html

and VBA and Workbook-level protection aren't particularly better:

http://www.mcgimpsey.com/excel/fileandvbapwords.html

(and a simple hex editor will reveal most of the data, even if the
student doesn't have XL).

That said, VLOOKUP is just a specialized form of INDEX(MATCH()), so
which implementation you use depends on whether the ID is at the first
column of data or not. For instance, if the ID is in column A:

=VLOOKUP(AA1,A:Z,26,FALSE)

will return the value in column Z corresponding to the match, while if
the ID is in column J instead, this will return the corresponding value
in column B:

=INDEX(B:B, MATCH(AA1,J:J,FALSE))

Note that, even if you use hidden sheets, the unique student ID number
isn't a password - it's probably easily obtainable from other sources.
But if not, giving access to a workbook with names and IDs will fix
that...

 

[Father Jack]

JE: I appreciate your concern for security, but, in fact, students already
have access to this information in multiple places--both online and on
physical bulletin boards. I'm trying to retrieve a whole row of data,
rather than a single cell. Is that possible?

Father Jack

 

[JE McGimpsey]

Use VLOOKUP...