Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how do i edit link source in whole workbook? | Excel Discussion (Misc queries) | |||
Workbook with phantom link | Links and Linking in Excel | |||
How do I link to a workbook whose name is variable? | Excel Discussion (Misc queries) | |||
how do i link a list of items in a workbook to worksheets in the . | Excel Discussion (Misc queries) | |||
Link to password protected workbook | Excel Discussion (Misc queries) |