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")

Tom, have you read all the posts....do you have another suggestion! The
workbook is acutally open. When the template opens, a open dialog appears
for them to select and open the file that they want to take the data from -
it just minimizes itself as if it wasn't open. Then the name of that file
gets put in cell A1. Then in a formula I would like to say =[a1]sheet1!g26
and return the value that is in cell G26 of the file path name from a1...make
sense?

Thanks for your help.

Cathy

"Tom Ogilvy" wrote:

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")

If you only put the name of the file (and not the path) in the cell and the
file is open, then J.E.'s solution will work.

Producing the equivalent of

='[Myworkbook.xls]Sheet1'!G26

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

if
Myworkbook.xls
alone is found in A1.

--
Regards.
Tom Ogilvy


"Cathy W" wrote in message
...
Tom, have you read all the posts....do you have another suggestion! The
workbook is acutally open. When the template opens, a open dialog appears
for them to select and open the file that they want to take the data
from -
it just minimizes itself as if it wasn't open. Then the name of that file
gets put in cell A1. Then in a formula I would like to say
=[a1]sheet1!g26
and return the value that is in cell G26 of the file path name from
a1...make
sense?

Thanks for your help.

Cathy

"Tom Ogilvy" wrote:

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! :-)

Hi both. Thank you guys so much for your help...but I still get the #REF
error when I use the following code:

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

I am going to paste all my code here that I have put under the This Workbook
object. Should that maybe be somewhere else. I am starting to loose my mind
I think! Anyway here is the code I have in total. This code doesn't reflect
the new filename without the path.

Private Sub Workbook_Open()

Cells(1, 1).ClearContents

Application.DisplayAlerts = True
UpdateLinks = xlUpdateLinksAlways

'If ThisWorkbook.Path = "" Then

Call openfile
UpdateLinks = xlUpdateLinksAlways


End Sub

Private Sub openfile()

Dim sDailyReport As String
Dim vFileName As Variant

ChDrive "K"
ChDir "K:\Individual Directories\Woodford, Cathy\Daily Report"
filetoopen = Application.GetOpenFileName("Excel Files (*.xls), *.xls")

If filetoopen < False Then
Workbooks.Open FileName:=filetoopen
Else
MsgBox "User Clicked Cancel, Exiting"
Exit Sub
End If


ActiveWindow.WindowState = xlMinimized
ActiveWindow.WindowState = xlMaximized

Sheets(1).Cells(1, 1).Value = BuildString(filetoopen)
Sheets(2).Cells(1, 1).Value = BuildString(filetoopen)
Sheets(3).Cells(1, 1).Value = BuildString(filetoopen)
Sheets(4).Cells(1, 1).Value = BuildString(filetoopen)
Sheets(5).Cells(1, 1).Value = BuildString(filetoopen)


End Sub

Public Function BuildString(vStr As Variant)
sStr = vStr
If InStr(sStr, "\") = 0 Then
BuildString = "[" & sStr & "]"
Else
sStr1 = sStr
Do While InStr(sStr1, "\") 0
sStr1 = Right(sStr1, Len(sStr1) - InStr(sStr1, "\"))
Loop
BuildString = Left(sStr, Len(sStr) - Len(sStr1)) _
& "[" & sStr1 & "]"
End If
End Function

Thanks again guys...you are being very kind to keep looking at this.

Cathy

"JE McGimpsey" wrote:

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! :-)

Hi again both. Don't bother with looking at the code...it was my own stupid
mistake...I didn't have the right sheet name. Now I do have another question
though. This is on a template and when the user double clicks the template
it automatically names it a .xls. How do I go about saving this file with no
code and no formulas...do I put code in the save as function or where.

Thanks again guys.

Cathy

"Cathy W" wrote:

Hi both. Thank you guys so much for your help...but I still get the #REF
error when I use the following code:

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

I am going to paste all my code here that I have put under the This Workbook
object. Should that maybe be somewhere else. I am starting to loose my mind
I think! Anyway here is the code I have in total. This code doesn't reflect
the new filename without the path.

Private Sub Workbook_Open()

Cells(1, 1).ClearContents

Application.DisplayAlerts = True
UpdateLinks = xlUpdateLinksAlways

'If ThisWorkbook.Path = "" Then

Call openfile
UpdateLinks = xlUpdateLinksAlways


End Sub

Private Sub openfile()

Dim sDailyReport As String
Dim vFileName As Variant

ChDrive "K"
ChDir "K:\Individual Directories\Woodford, Cathy\Daily Report"
filetoopen = Application.GetOpenFileName("Excel Files (*.xls), *.xls")

If filetoopen < False Then
Workbooks.Open FileName:=filetoopen
Else
MsgBox "User Clicked Cancel, Exiting"
Exit Sub
End If


ActiveWindow.WindowState = xlMinimized
ActiveWindow.WindowState = xlMaximized

Sheets(1).Cells(1, 1).Value = BuildString(filetoopen)
Sheets(2).Cells(1, 1).Value = BuildString(filetoopen)
Sheets(3).Cells(1, 1).Value = BuildString(filetoopen)
Sheets(4).Cells(1, 1).Value = BuildString(filetoopen)
Sheets(5).Cells(1, 1).Value = BuildString(filetoopen)


End Sub

Public Function BuildString(vStr As Variant)
sStr = vStr
If InStr(sStr, "\") = 0 Then
BuildString = "[" & sStr & "]"
Else
sStr1 = sStr
Do While InStr(sStr1, "\") 0
sStr1 = Right(sStr1, Len(sStr1) - InStr(sStr1, "\"))
Loop
BuildString = Left(sStr, Len(sStr) - Len(sStr1)) _
& "[" & sStr1 & "]"
End If
End Function

Thanks again guys...you are being very kind to keep looking at this.

Cathy

"JE McGimpsey" wrote:

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! :-)