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.

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.

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.

Hi!

If you simply drag copy down this formula:

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

It will automatically increment to:

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

But, if you want to make it row specific:

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

If I understand Problem 2:

Since you say a specific sheet *always* refers to a specific row in the
other file you can simply hard code that into the formula:

=INDEX([yetanotherfile.xls]sheet1!$1:$1,ROWS($1:1))
=INDEX([yetanotherfile.xls]sheet1!$5:$5,ROWS($1:1))

But, if you want the row to be variable and use cell A1 to hold that
variable:

=INDEX(INDIRECT("'[yetanotherfile.xls]sheet1'!"&A$1&":"&A$1),ROWS($1:1))

NB: the use of a lot of volatile functions (Indirect is volatile) can cause
your files to slow down as they recalculate every time a calculation takes
place!

Biff

"jbart" wrote in message
...
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.

Thanks Biff! I would have never been able to figure those out without your
help.

"Biff" wrote:

Hi!

If you simply drag copy down this formula:

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

It will automatically increment to:

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

But, if you want to make it row specific:

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

If I understand Problem 2:

Since you say a specific sheet *always* refers to a specific row in the
other file you can simply hard code that into the formula:

=INDEX([yetanotherfile.xls]sheet1!$1:$1,ROWS($1:1))
=INDEX([yetanotherfile.xls]sheet1!$5:$5,ROWS($1:1))

But, if you want the row to be variable and use cell A1 to hold that
variable:

=INDEX(INDIRECT("'[yetanotherfile.xls]sheet1'!"&A$1&":"&A$1),ROWS($1:1))

NB: the use of a lot of volatile functions (Indirect is volatile) can cause
your files to slow down as they recalculate every time a calculation takes
place!

Biff

"jbart" wrote in message
...
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.