link to other workbook with changing filename
 

Hi,

I have two helper cells with values:
L3 = [Trials AM05 30Aug06.xls]
L4 = T AM06 A 30Aug06 usd

I am trying to recreate this:
='[Trials AM05 30Aug06.xls]T AM05 A 30Aug06 usd '!$F$38"

using:
="'" & L3 & L4 & "'"!$F$38" but it says there's an error.

What's wrong with my formula? Any help would be greatly apprciated.

Dave.

link to other workbook with changing filename
 

You're close... you need one more function, INDIRECT():

=INDIRECT("'" & L3 & L4 & "'"!$F$38")

--
Regards,
Dave


"DN" wrote:

Hi,

I have two helper cells with values:
L3 = [Trials AM05 30Aug06.xls]
L4 = T AM06 A 30Aug06 usd

I am trying to recreate this:
='[Trials AM05 30Aug06.xls]T AM05 A 30Aug06 usd '!$F$38"

using:
="'" & L3 & L4 & "'"!$F$38" but it says there's an error.

What's wrong with my formula? Any help would be greatly apprciated.

Dave.

link to other workbook with changing filename
 

Hi David,

Thanks for you quick reply but I still get an error and it highlights the
second set of "'" in the formula. Any thoughts?

"David Billigmeier" wrote:

You're close... you need one more function, INDIRECT():

=INDIRECT("'" & L3 & L4 & "'"!$F$38")

--
Regards,
Dave


"DN" wrote:

Hi,

I have two helper cells with values:
L3 = [Trials AM05 30Aug06.xls]
L4 = T AM06 A 30Aug06 usd

I am trying to recreate this:
='[Trials AM05 30Aug06.xls]T AM05 A 30Aug06 usd '!$F$38"

using:
="'" & L3 & L4 & "'"!$F$38" but it says there's an error.

What's wrong with my formula? Any help would be greatly apprciated.

Dave.

link to other workbook with changing filename
 

Hi


"David Billigmeier" wrote in message
...
You're close... you need one more function, INDIRECT():

=INDIRECT("'" & L3 & L4 & "'"!$F$38")


.... and don't forget to open acording workbook every time before changing
the entry in L3. INDIRECT works only when the source workbook is open too -
otherwise you get an error.

Arvi Laanemets

link to other workbook with changing filename
 

Ahh, yep, there it is... missing a quotation and an ampersand:

=INDIRECT("'"&L3&L4&"'"&"!F38")

--
Regards,
Dave


"DN" wrote:

Hi David,

Thanks for you quick reply but I still get an error and it highlights the
second set of "'" in the formula. Any thoughts?

"David Billigmeier" wrote:

You're close... you need one more function, INDIRECT():

=INDIRECT("'" & L3 & L4 & "'"!$F$38")

--
Regards,
Dave


"DN" wrote:

Hi,

I have two helper cells with values:
L3 = [Trials AM05 30Aug06.xls]
L4 = T AM06 A 30Aug06 usd

I am trying to recreate this:
='[Trials AM05 30Aug06.xls]T AM05 A 30Aug06 usd '!$F$38"

using:
="'" & L3 & L4 & "'"!$F$38" but it says there's an error.

What's wrong with my formula? Any help would be greatly apprciated.

Dave.

link to other workbook with changing filename
 

Thanks that works. But If the file that it links to is closed it doesn't
work. Would I have to include the filepath somehow? I tried C:\files\ in a
helper cell L2 and then changed it to =INDIRECT("'"&L2&L3&L4&"'"&"!$F$38")
but it just gives me #REF!




"David Billigmeier" wrote:

Ahh, yep, there it is... missing a quotation and an ampersand:

=INDIRECT("'"&L3&L4&"'"&"!F38")

--
Regards,
Dave


"DN" wrote:

Hi David,

Thanks for you quick reply but I still get an error and it highlights the
second set of "'" in the formula. Any thoughts?

"David Billigmeier" wrote:

You're close... you need one more function, INDIRECT():

=INDIRECT("'" & L3 & L4 & "'"!$F$38")

--
Regards,
Dave


"DN" wrote:

Hi,

I have two helper cells with values:
L3 = [Trials AM05 30Aug06.xls]
L4 = T AM06 A 30Aug06 usd

I am trying to recreate this:
='[Trials AM05 30Aug06.xls]T AM05 A 30Aug06 usd '!$F$38"

using:
="'" & L3 & L4 & "'"!$F$38" but it says there's an error.

What's wrong with my formula? Any help would be greatly apprciated.

Dave.

link to other workbook with changing filename
 

Hi

One possible solution:

Into workbook module, create some procedure like this
***
Public Sub AddLink()
ActiveCell.Formula = "='" & Sheets("MySheet").Range("L3").Value &
Sheets("MySheet").Range("L4").Value & "'!" & ActiveCell.Address
End Sub
***

Instead MySheet, use the sheet name where file and sheet names for link are
stored.
When you now activate any cell in workbook, and run the procedure AddLink,
the link to same cell address on sheet in workbook, determined in
MySheet!L3:L4, as for active cell is created.
I.e. when p.e. you activated cell Sheet3!B12, in cell MySheet!L3 was string
"C:\Documents and settings\MyProfile\My Documents\[Test.xls]", and in cell
MySheet!L4 was string "TestSheet", then into active cell is inserted a link
='C:\Documents and settings\MyProfile\My
Documents\[Test.xls]TestSheet'!$B$12


Arvi Laanemets


"DN" wrote in message
...
Thanks that works. But If the file that it links to is closed it doesn't
work. Would I have to include the filepath somehow? I tried C:\files\ in
a
helper cell L2 and then changed it to =INDIRECT("'"&L2&L3&L4&"'"&"!$F$38")
but it just gives me #REF!




"David Billigmeier" wrote:

Ahh, yep, there it is... missing a quotation and an ampersand:

=INDIRECT("'"&L3&L4&"'"&"!F38")

--
Regards,
Dave


"DN" wrote:

Hi David,

Thanks for you quick reply but I still get an error and it highlights
the
second set of "'" in the formula. Any thoughts?

"David Billigmeier" wrote:

You're close... you need one more function, INDIRECT():

=INDIRECT("'" & L3 & L4 & "'"!$F$38")

--
Regards,
Dave


"DN" wrote:

Hi,

I have two helper cells with values:
L3 = [Trials AM05 30Aug06.xls]
L4 = T AM06 A 30Aug06 usd

I am trying to recreate this:
='[Trials AM05 30Aug06.xls]T AM05 A 30Aug06 usd '!$F$38"

using:
="'" & L3 & L4 & "'"!$F$38" but it says there's an error.

What's wrong with my formula? Any help would be greatly
apprciated.

Dave.