File name and Sheet name a variable.

[Duane]

I know this has been asked, and I have tried to figure out the ansewers but
without success. I will ask how I am trying to do it.

the first part is as follows:

A cell contains the name of a work sheet in a different workbook. If the
cell value changes, I would like the new name to look to that worksheet in
the different workbook.

={MH06S0Aug08dy.xls]Sheet1!H$13

"Sheet1" is the variable and would like it to refer to a cell value. I
would like it to work if the workbook it refers to is closed as well.

Part two is being able to have the file name a variable as well, but this
part is not nearly as time saving as the first part of the question would be.

Thanks for all the help in this forum. It is appreciated.

 

[Jim Thomlinson]

You are looking for the indirect functions, but indirect does not work with
closed workbooks. In order to get around that you need to get the morefunc
addin:

http://xcell05.free.fr/morefunc/english/index.htm

Note that you are going to need to specify the entire file and path name for
the closed file as the function will have no way of knowing which file it is
without the full path.

 

[Duane]

Is there any chance that Office 2007 works around it without the add in? Or
will Office 2010?

In the meantime, if 2007 doesnt, I will try the add in.

Thanks

You are looking for the indirect functions, but indirect does not work with
closed workbooks. In order to get around that you need to get the morefunc
addin:

http://xcell05.free.fr/morefunc/english/index.htm

Note that you are going to need to specify the entire file and path name for
the closed file as the function will have no way of knowing which file it is
without the full path.
--
HTH...

Jim Thomlinson

I know this has been asked, and I have tried to figure out the ansewers but
without success. I will ask how I am trying to do it.

the first part is as follows:

A cell contains the name of a work sheet in a different workbook. If the
cell value changes, I would like the new name to look to that worksheet in
the different workbook.

={MH06S0Aug08dy.xls]Sheet1!H$13

"Sheet1" is the variable and would like it to refer to a cell value. I
would like it to work if the workbook it refers to is closed as well.

Part two is being able to have the file name a variable as well, but this
part is not nearly as time saving as the first part of the question would be.

Thanks for all the help in this forum. It is appreciated.