File name and Sheet name a variable.

D

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.
 
J

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.
 
D

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

Jim Thomlinson said:
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


Duane said:
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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top