Mark wrote:
Thanks, but I guess I don't know what you mean by column identifiers.

"smartin" wrote:

Bob Bridges wrote:

[snippens]

VLOOKUP(A30,'[Repository.xls]Grade 4'!$A$1:$X$23,COLUMN(),0)

Now the third argument is a 2 in column B, a 3 in column C and so on. That
formula you can copy throughout the whole range and it'll pull all the right
grades from the repository.

You can stop there and it'll work, but for future reference I'll warn you
that this technique, of using the column number to determine the column
reference in VLOOKUP, has a weakness: The next time you insert or delete a
column, anything to the right of it that depends on this technique will be
out of kilter, and you'll have to change every reference. Worse, it won't
necessarily be immediately obvious that it happened; after all, if your
column E shows the grade from column F, would you notice right away? I
maintain there must be a way around this weakness so I can get the best of
both, but I haven't sat down and figured it out yet.
One alternative is to place column identifiers in both worksheets, and
use INDEX/MATCH. This gets around using a relative column reference so
the formula will not break if a column is inserted somewhere.

It might look like this in cell Z30 (this is an array formula, complete
with Ctrl+Shift+Enter):

=INDEX('[Repository.xls]Grade 4'!$A$1:$X$23,
MATCH(1,--($A30='[Repository.xls]Grade 4'!$A$1:$A$23),0),
MATCH(1,--(Z$1='[Repository.xls]Grade 4'!$A$1:$X$1),0))

(line breaks entered for clarity)

This assumes row $1 has the column identifiers you want to coordinate.


Sorry I was not being totally clear.

In '[Repository.xls]Grade 4' suppose row 1 has labels (column
identifiers) like "Assignment 1", "Assignment 2", etc. This kind of
layout is typical.

For this example, in the worksheet looking up the values, place the
labels you want to look up in row 1. E.g., put "Assignment 1" in Z1.

Notice the 3rd parameter of the INDEX formula is
MATCH(1,--(Z$1='[Repository.xls]Grade 4'!$A$1:$X$1),0)

This basically says, "Get the value in row one and find it in the first
row of '[Repository.xls]Grade 4'. Use the column where we matched to
return the result."

You could also use VLOOKUP in this way as well. In the col_index_num
parameter, substitute the same MATCH.

Hope this helps!