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 said:
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 said:
Ahh, yep, there it is... missing a quotation and an ampersand:
=INDIRECT("'"&L3&L4&"'"&"!F38")
--
Regards,
Dave
DN said:
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?
:
You're close... you need one more function, INDIRECT():
=INDIRECT("'" & L3 & L4 & "'"!$F$38")
--
Regards,
Dave
:
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.
