Hello. I have an excel template that when opened, pops up an open dialog box
and asks the user to pick a file to open. When this file is opened, the path
is then inserted into cell A1 of all sheets of the template. I then want to
use the formula in certain cells that takes values from the workbook that the
user selected to open. An example of the formula is : =[A1]Sheet1!G26. What
I thought this would do would be to pull the path from cell A1 and then go to
sheet 1, cell G26 and get that value, but it is not updating. I need it like
this because the values of the cells depend on the file the user opens.

Hope someone can help.

Cathy

Try

=INDIRECT(["'" & A1 & "]Sheet1'!G26")


In article ,
"Cathy W" wrote:

Hello. I have an excel template that when opened, pops up an open dialog box
and asks the user to pick a file to open. When this file is opened, the path
is then inserted into cell A1 of all sheets of the template. I then want to
use the formula in certain cells that takes values from the workbook that the
user selected to open. An example of the formula is : =[A1]Sheet1!G26. What
I thought this would do would be to pull the path from cell A1 and then go to
sheet 1, cell G26 and get that value, but it is not updating. I need it like
this because the values of the cells depend on the file the user opens.

Hope someone can help.

Cathy

Hi. Thanks for the response.

It gives me the error "That name is not valid" and highlights the "'" quote
in the formula.


"JE McGimpsey" wrote:

Try

=INDIRECT(["'" & A1 & "]Sheet1'!G26")


In article ,
"Cathy W" wrote:

Hello. I have an excel template that when opened, pops up an open dialog box
and asks the user to pick a file to open. When this file is opened, the path
is then inserted into cell A1 of all sheets of the template. I then want to
use the formula in certain cells that takes values from the workbook that the
user selected to open. An example of the formula is : =[A1]Sheet1!G26. What
I thought this would do would be to pull the path from cell A1 and then go to
sheet 1, cell G26 and get that value, but it is not updating. I need it like
this because the values of the cells depend on the file the user opens.

Hope someone can help.

Cathy

Yup - bad paste on my part:

=INDIRECT("'[" & A1 & "]Sheet1'!G26")

In article ,
"Cathy W" wrote:

Hi. Thanks for the response.

It gives me the error "That name is not valid" and highlights the "'" quote
in the formula.


"JE McGimpsey" wrote:

Try

=INDIRECT(["'" & A1 & "]Sheet1'!G26")

Hi. Thanks again for the response. I am going to paste what is put in cell
A1 so that you get an idea of what's happening. The formula still returns a
#REF! error.

In cell A1, the path of the file the user selects in the open dialog box
appears as the following:

K:\INDIVIDUAL DIRECTORIES\Woodford, Cathy\Daily Report\dec 12.05.xls

So then in cell E12 I want it to say =[a1]dos1!g26 which should return the
following formula: =[K:\INDIVIDUAL DIRECTORIES\Woodford, Cathy\Daily
Report\dec 12.05.xls]dos1!g26 but it's like it's not picking up the value in
cell A1.

HELP! :-)

"JE McGimpsey" wrote:

Yup - bad paste on my part:

=INDIRECT("'[" & A1 & "]Sheet1'!G26")

In article ,
"Cathy W" wrote:

Hi. Thanks for the response.

It gives me the error "That name is not valid" and highlights the "'" quote
in the formula.


"JE McGimpsey" wrote:

Try

=INDIRECT(["'" & A1 & "]Sheet1'!G26")

It should return

=K:\INDIVIDUAL DIRECTORIES\Woodford, Cathy\Daily Report\[dec
12.05.xls]dos1!g26

If you want It correct, but refreshingly, it won't make any difference since
Indirect does not work with a closed workbook.

--
Regards,
Tom Ogilvy


"Cathy W" wrote in message
...
Hi. Thanks again for the response. I am going to paste what is put in
cell
A1 so that you get an idea of what's happening. The formula still returns
a
#REF! error.

In cell A1, the path of the file the user selects in the open dialog box
appears as the following:

K:\INDIVIDUAL DIRECTORIES\Woodford, Cathy\Daily Report\dec 12.05.xls

So then in cell E12 I want it to say =[a1]dos1!g26 which should return the
following formula: =[K:\INDIVIDUAL DIRECTORIES\Woodford, Cathy\Daily
Report\dec 12.05.xls]dos1!g26 but it's like it's not picking up the value
in
cell A1.

HELP! :-)

"JE McGimpsey" wrote:

Yup - bad paste on my part:

=INDIRECT("'[" & A1 & "]Sheet1'!G26")

In article ,
"Cathy W" wrote:

Hi. Thanks for the response.

It gives me the error "That name is not valid" and highlights the "'"
quote
in the formula.


"JE McGimpsey" wrote:

Try

=INDIRECT(["'" & A1 & "]Sheet1'!G26")

Since the file will be open, there's no need for the path (and
INDIRECT() doesn't work with closed workbooks), so I'd suggest modifying
the Workbook_Open code to just put the workbook name in.

If you can't do that, then you can use something like:

=INDIRECT("'[" & MID(A1, FIND("$$", SUBSTITUTE(A1, "\", "$$", LEN(A1)
- LEN(SUBSTITUTE(A1, "\", "")))) + 1, 255) & "]Sheet1'!G26")


In article ,
"Cathy W" wrote:

Hi. Thanks again for the response. I am going to paste what is put in cell
A1 so that you get an idea of what's happening. The formula still returns a
#REF! error.

In cell A1, the path of the file the user selects in the open dialog box
appears as the following:

K:\INDIVIDUAL DIRECTORIES\Woodford, Cathy\Daily Report\dec 12.05.xls

So then in cell E12 I want it to say =[a1]dos1!g26 which should return the
following formula: =[K:\INDIVIDUAL DIRECTORIES\Woodford, Cathy\Daily
Report\dec 12.05.xls]dos1!g26 but it's like it's not picking up the value in
cell A1.

HELP! :-)