Thanks Biff. Ok, I got that working. Now it's getting more complicated. I
need to make the row numbers variable.

Now what if for each row I want it to use the cell in that row of column B.
So for example the formula in row 1 would be:

=INDIRECT("'[anotherfile.xls]"&B1&"'!A1")

but the formula in row 2 needs to be:

=INDIRECT("'[anotherfile.xls]"&B2&"'!A1")

I want to copy this formula down for a few hundred rows. Is there a way to
incorporate ROW() into this formula so that it works everywhere without
having to change the column?

PROBLEM 2:
My second problem is basically the same. I have hundreds of worksheets in a
workbook. Each one pulls some data from a single worksheet. Think of it as
each sheet represents someone and the other sheet is an address list. I'm
trying to populate each person's sheet with the address information from the
master sheet.

So each person has a sheet and cell A1 contains the row number to be used
from the master sheet. For example SheetA always pulls from row 1 of
yetanotherfile.xls while SheetB always pulls from row 5 of
yetanotherfile.xls, and so on. So:

SheetA cell A1 = 1
SheetA cell B1 ='[yetanotherfile.xls]Sheet1'!$A$1
SheetA cell B2 ='[yetanotherfile.xls]Sheet1'!$B$1
SheetA cell B3 ='[yetanotherfile.xls]Sheet1'!$C$1

SheetB cell A1 = 5
SheetB cell B1 ='[yetanotherfile.xls]Sheet1'!$A$5
SheetB cell B2 ='[yetanotherfile.xls]Sheet1'!$B$5
SheetB cell B3 ='[yetanotherfile.xls]Sheet1'!$C$5

How can I make the row number dependent on the value in cell A1 of the
current sheet? I'm thinking INDIRECT would work again but it's not accepting
it.

I want something like:

=INDIRECT("'[yetanotherfile.xls]Sheet1'!$A$&A1&)

Thank you very much!


"Biff" wrote:

Hi!

You need a formula like this:

=INDIRECT("'[anotherfile.xls]"&B2&"'!A1")

However, this will only work if the other file is open.

Biff

"jbart" wrote in message
...
I have two spreadsheets that I need this for, so I don't think this problem
is unique. I know how to get data from another worksheet. But what if
the
name of the sheet that I want to pull it from is dependent on something on
my
current worksheet. For example I use this formula right now which works
fine.

='[anotherfile.xls]Jeff'!$A$1

But I want to be able to pull from different sheets based on values in my
existing sheet. So basically I want to be able to replace 'Jeff' in the
above formula with something like "B2" where B2 could be "Jeff", "Jay",
"Joe", etc. Note that B2 is located in the current worksheet, not in
[otherfile.xls]Jeff.

If I use ='[anotherfile.xls]B2'!$A$1 I get an error. Hopefully this is
clear.

Thanks.