cell range lookup to another workbook

  • Thread starter Thread starter FJDx
  • Start date Start date
F

FJDx

In Book1, I have several rows of data (each of four columns long), each
given a named range of Group1, Group2, Group3 etc.

In Book2, cell A1, I have the text "Group3". I would therefore like to have
the data Group3 displayed in cells A2....A5. The only way I can get this to
work is if I enter the formula =Book1!Group3 and fill right. However, I
would like this formula to do a lookup of cell A1 to see which named range
it should display.

I cannot do a vlookup as the cells in Book1 are set in a special order so I
would not be able to put row headers in and sort them alphabetically. Book1
should always remain in the same format (though the data within the ranges
may vary) but I would like to have Book2, Book3, Book4 etc all linking into
Book1.

Thanks!
 
Book1 must be open for this formula to work. Enter the following in Book2,
cell A2:

=INDEX(INDIRECT("Book1.xls!"&$A$1),1,ROW()-1)

Copy down to A5.
 
Hello Laura,

Thanks this worked well... but I have just noticed an error in my question.
Group3 should be displayed in cells B1 to E1 (ie going across the way to
the right of cell A1.

I changed the Row()-1 to Column()-1 to test this and it worked fine.
However, what does this part actually mean? I just guessed that this might
be the problem.
 
Good guess, you made the correct adjustment.

The INDEX function has three (or four, if needed) arguments.

In the formula I gave you, the first argument (Array) contained the
following:

INDIRECT("Book1.xls!"&$A$1)

The INDIRECT function is used to make a text string into an actual
reference. with "Group3" in A1, Excel would read this as: Book1.xls!Group1
(an array, which is what is requested for that first argument in the INDEX
function.

The second argument (Row_num) contained a "1". That tells Excel to pull
from the first row of the array. As each of your named arrays contain only
one row, this number is a constant.

The third and final argument (Column_num) contained the following (as edited
and corrected by you):

COLUMN()-1

The function COLUMN returns the column number of a reference. If no
reference is entered (i.e. "()" instead of "(A1)") then excel returns the
column number of the cell containing the formula. The same is true of the
ROW function, only it returns the row number. I included the "-1" because
the cells containing the formula were all "off" by 1. For example, the
formula in Book2, cell B1 would have returned 2 for the COLUMN part of the
function, but we/you wanted the function to pull the first (or column 1)
from the array.

If you have any other question, or I did not explain this well (which
happens), please let me know.
 
Good guess, you made the correct adjustment.

The INDEX function has three (or four, if needed) arguments.

In the formula I gave you, the first argument (Array) contained the
following:

INDIRECT("Book1.xls!"&$A$1)

The INDIRECT function is used to make a text string into an actual
reference. with "Group3" in A1, Excel would read this as: Book1.xls!Group1
(an array, which is what is requested for that first argument in the INDEX
function.

The second argument (Row_num) contained a "1". That tells Excel to pull
from the first row of the array. As each of your named arrays contain only
one row, this number is a constant.

The third and final argument (Column_num) contained the following (as edited
and corrected by you):

COLUMN()-1

The function COLUMN returns the column number of a reference. If no
reference is entered (i.e. "()" instead of "(A1)") then excel returns the
column number of the cell containing the formula. The same is true of the
ROW function, only it returns the row number. I included the "-1" because
the cells containing the formula were all "off" by 1. For example, the
formula in Book2, cell B1 would have returned 2 for the COLUMN part of the
function, but we/you wanted the function to pull the first (or column 1)
from the array.

If you have any other question, or I did not explain this well (which
happens), please let me know.
 
Laura Cook said:
Good guess, you made the correct adjustment.

The INDEX function has three (or four, if needed) arguments.

In the formula I gave you, the first argument (Array) contained the
following:

INDIRECT("Book1.xls!"&$A$1)

The INDIRECT function is used to make a text string into an actual
reference. with "Group3" in A1, Excel would read this as: Book1.xls!Group1
(an array, which is what is requested for that first argument in the INDEX
function.

The second argument (Row_num) contained a "1". That tells Excel to pull
from the first row of the array. As each of your named arrays contain only
one row, this number is a constant.

The third and final argument (Column_num) contained the following (as edited
and corrected by you):

COLUMN()-1

The function COLUMN returns the column number of a reference. If no
reference is entered (i.e. "()" instead of "(A1)") then excel returns the
column number of the cell containing the formula. The same is true of the
ROW function, only it returns the row number. I included the "-1" because
the cells containing the formula were all "off" by 1. For example, the
formula in Book2, cell B1 would have returned 2 for the COLUMN part of the
function, but we/you wanted the function to pull the first (or column 1)
from the array.

If you have any other question, or I did not explain this well (which
happens), please let me know.
Click to expand...

Thanks Laura, this has been very helpful!
 
Back
Top