Linking to other workbooks question

[Guest]

I use excel2003, and here's my situation.

I have a master workbook which contains link to other feeder workbooks which
gets data filled in by others. When the feeder goes from draft to issue, the
filename would change. Then, this set of master and feeder workbooks would
get copied into another phase of work then being saved with another filename.
I am likely to have up to 10 feeder files being updated every couple of
days. Each of the feeder would have lots of cells feeding into the master
xls.

My question is this:
Is there a way that I can input the filenames of the feeder workbooks into
the master and have that as part of the formula to link into the feeder?

For example:

In the master workbook
Cell A1 = 'c:\excel\[feeder.xls]sheet1'! (text string)
Cell A2 = A1&"$B$1" (links to cell B1 in feeder.xls)

The result I get is #REF.

I want to do this because I want to be able to just change values in Cell A1
of the master workbook to link to a new version of the workbook, say
"feeder_newVersion.xls".


Maybe I am just out of my mind, but any tips would be appreciated. Thanks..

 

[Guest]

try indirect
in a2 enter
=indirect(A1&"$B$1")

 

[Guest]

It didn't work. Still #REF.
I even tried
= indirect(T(A1)&"$B$1)

try indirect
in a2 enter
=indirect(A1&"$B$1")

I use excel2003, and here's my situation.

I have a master workbook which contains link to other feeder workbooks which
gets data filled in by others. When the feeder goes from draft to issue, the
filename would change. Then, this set of master and feeder workbooks would
get copied into another phase of work then being saved with another filename.
I am likely to have up to 10 feeder files being updated every couple of
days. Each of the feeder would have lots of cells feeding into the master
xls.

My question is this:
Is there a way that I can input the filenames of the feeder workbooks into
the master and have that as part of the formula to link into the feeder?

For example:

In the master workbook
Cell A1 = 'c:\excel\[feeder.xls]sheet1'! (text string)
Cell A2 = A1&"$B$1" (links to cell B1 in feeder.xls)

The result I get is #REF.

I want to do this because I want to be able to just change values in Cell A1
of the master workbook to link to a new version of the workbook, say
"feeder_newVersion.xls".


Maybe I am just out of my mind, but any tips would be appreciated. Thanks..

 

[Dave Peterson]

The function you'd want to use that's built into excel is =indirect(). But that
function returns an error if the sending workbook is closed.

Laurent Longre has an addin (morefunc.xll) at:
http://xcell05.free.fr/

That includes =indirect.ext() that may help you.

I use excel2003, and here's my situation.

I have a master workbook which contains link to other feeder workbooks which
gets data filled in by others. When the feeder goes from draft to issue, the
filename would change. Then, this set of master and feeder workbooks would
get copied into another phase of work then being saved with another filename.
I am likely to have up to 10 feeder files being updated every couple of
days. Each of the feeder would have lots of cells feeding into the master
xls.

My question is this:
Is there a way that I can input the filenames of the feeder workbooks into
the master and have that as part of the formula to link into the feeder?

For example:

In the master workbook
Cell A1 = 'c:\excel\[feeder.xls]sheet1'! (text string)
Cell A2 = A1&"$B$1" (links to cell B1 in feeder.xls)

The result I get is #REF.

I want to do this because I want to be able to just change values in Cell A1
of the master workbook to link to a new version of the workbook, say
"feeder_newVersion.xls".

Maybe I am just out of my mind, but any tips would be appreciated. Thanks..

 

[Guest]

Hi,

See online help "Merge workbooks" and "Troubleshoot merging workbooks '

Challa Prabhu