If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Referencing another worksheet
I am using a text string to reference a block of cells in another worksheet.
The text string is in a seperate cell, and I am using this string in a multitude of other cells using INDIRECT(TextStringCell). If the text string is in the form [Workbook]Worksheet!A1:B2, for example, i have to have the referenced workbook open for the link to work. I thought perhaps that if the string was in the form C:\Directory\[Workbook]Worksheet!A1:B2, i needn't have the referenced workbook open. Needless to say, this doesn't work. On other spreadsheets, where i do not call a reference indirectly, i can happily use the function ='C:\Directory\[Workbook]Worksheet!A1:B2 and I do not need to have the referenced workbook open. Is this problem just a quirk of an indirect reference, or is there a way around it? Perhaps I'm missing some apostrophes some where.... Thanks in advance. |
#2
|
|||
|
|||
INDIRECT only works with open workbooks.
-- HTH Bob Phillips "rmellison" wrote in message ... I am using a text string to reference a block of cells in another worksheet. The text string is in a seperate cell, and I am using this string in a multitude of other cells using INDIRECT(TextStringCell). If the text string is in the form [Workbook]Worksheet!A1:B2, for example, i have to have the referenced workbook open for the link to work. I thought perhaps that if the string was in the form C:\Directory\[Workbook]Worksheet!A1:B2, i needn't have the referenced workbook open. Needless to say, this doesn't work. On other spreadsheets, where i do not call a reference indirectly, i can happily use the function ='C:\Directory\[Workbook]Worksheet!A1:B2 and I do not need to have the referenced workbook open. Is this problem just a quirk of an indirect reference, or is there a way around it? Perhaps I'm missing some apostrophes some where.... Thanks in advance. |
#3
|
|||
|
|||
Hi
INDIRECT doesn't work with closed workbooks. you have to use some different solution. a) When the number of workbooks you are linking to is limited, then you can mirror them on some hidden sheet. Now you refer to this/those hidden sheet(s) instead of files (I have a project where ~20 workbooks are linked to summary workbook in such a way). b) Some frequent resident of Excel NG's (I don't remember who) has an UDF or Add-In on his site to download, which is an equivalent for INDIRECT, but works with closed workbooks too. But when you want to design a workbook for use by several users, all of them must have it in their computers - which can be a problem. c) Design your workbook to link to some fixed workbook. When you want to swich the source workbook, you save it with this fixed name (not a good solution, but for some occassions will do). d) Create a procedure, which asks for workbook you want to link to, and rewrites then all links. You can start the procedure from hot key, or from button placed on worksheet. e) ... I'm sure there are other possible solutions, but for start it will do. -- Arvi Laanemets ( My real mail address: arvilattarkon.ee ) "rmellison" wrote in message ... I am using a text string to reference a block of cells in another worksheet. The text string is in a seperate cell, and I am using this string in a multitude of other cells using INDIRECT(TextStringCell). If the text string is in the form [Workbook]Worksheet!A1:B2, for example, i have to have the referenced workbook open for the link to work. I thought perhaps that if the string was in the form C:\Directory\[Workbook]Worksheet!A1:B2, i needn't have the referenced workbook open. Needless to say, this doesn't work. On other spreadsheets, where i do not call a reference indirectly, i can happily use the function ='C:\Directory\[Workbook]Worksheet!A1:B2 and I do not need to have the referenced workbook open. Is this problem just a quirk of an indirect reference, or is there a way around it? Perhaps I'm missing some apostrophes some where.... Thanks in advance. |
#4
|
|||
|
|||
"Arvi Laanemets" wrote in message ... b) Some frequent resident of Excel NG's (I don't remember who) has an UDF or Add-In on his site to download, which is an equivalent for INDIRECT, but works with closed workbooks too. But when you want to design a workbook for use by several users, all of them must have it in their computers - which can be a problem. That would be Harlan Grove who wrote a UDF called PULL. You can find the function at his FTP site: ftp://members.aol.com/hrlngrv/ Look for pull.zip |
#5
|
|||
|
|||
That would explain my problems!
Lots of good solutions there. The first suggestioon seems the most fitting for my purposes, since I only have 2 referenced workbooks. It would be easy enough just to have those two open, but its a pain when you forget or open the wrong one accidently. So, rather than mirror a whole load of sheets onto hidden sheets in my open file, i was wondering how easy it would to open the two referenced files automatically when I open the master file? (And close them automatically?) Could you do this with a macro? "Arvi Laanemets" wrote: Hi INDIRECT doesn't work with closed workbooks. you have to use some different solution. a) When the number of workbooks you are linking to is limited, then you can mirror them on some hidden sheet. Now you refer to this/those hidden sheet(s) instead of files (I have a project where ~20 workbooks are linked to summary workbook in such a way). b) Some frequent resident of Excel NG's (I don't remember who) has an UDF or Add-In on his site to download, which is an equivalent for INDIRECT, but works with closed workbooks too. But when you want to design a workbook for use by several users, all of them must have it in their computers - which can be a problem. c) Design your workbook to link to some fixed workbook. When you want to swich the source workbook, you save it with this fixed name (not a good solution, but for some occassions will do). d) Create a procedure, which asks for workbook you want to link to, and rewrites then all links. You can start the procedure from hot key, or from button placed on worksheet. e) ... I'm sure there are other possible solutions, but for start it will do. -- Arvi Laanemets ( My real mail address: arvilattarkon.ee ) "rmellison" wrote in message ... I am using a text string to reference a block of cells in another worksheet. The text string is in a seperate cell, and I am using this string in a multitude of other cells using INDIRECT(TextStringCell). If the text string is in the form [Workbook]Worksheet!A1:B2, for example, i have to have the referenced workbook open for the link to work. I thought perhaps that if the string was in the form C:\Directory\[Workbook]Worksheet!A1:B2, i needn't have the referenced workbook open. Needless to say, this doesn't work. On other spreadsheets, where i do not call a reference indirectly, i can happily use the function ='C:\Directory\[Workbook]Worksheet!A1:B2 and I do not need to have the referenced workbook open. Is this problem just a quirk of an indirect reference, or is there a way around it? Perhaps I'm missing some apostrophes some where.... Thanks in advance. |
#6
|
|||
|
|||
Will also try the PULL function. Thanks!
"Bob Phillips" wrote: "Arvi Laanemets" wrote in message ... b) Some frequent resident of Excel NG's (I don't remember who) has an UDF or Add-In on his site to download, which is an equivalent for INDIRECT, but works with closed workbooks too. But when you want to design a workbook for use by several users, all of them must have it in their computers - which can be a problem. That would be Harlan Grove who wrote a UDF called PULL. You can find the function at his FTP site: ftp://members.aol.com/hrlngrv/ Look for pull.zip |
#7
|
|||
|
|||
My VBA knowledge is very limited (usually to the record/stop button on the
macro toolbar). I have copied the code to a new module in my workbook, and the pull function appears in my UDF list. As a way of a test, I am just trying to reference one cell in a closed workbook using a string in A1, and =pull(a1) in another cell. Howewer, all i get is #value. Have tried with an open workbook, with and without the file path, with & without apostrophes.... Any suggestions? I get the feeling I'm over-complicating things now, but i've got this far.... "Bob Phillips" wrote: "Arvi Laanemets" wrote in message ... b) Some frequent resident of Excel NG's (I don't remember who) has an UDF or Add-In on his site to download, which is an equivalent for INDIRECT, but works with closed workbooks too. But when you want to design a workbook for use by several users, all of them must have it in their computers - which can be a problem. That would be Harlan Grove who wrote a UDF called PULL. You can find the function at his FTP site: ftp://members.aol.com/hrlngrv/ Look for pull.zip |
#8
|
|||
|
|||
Hi
"rmellison" wrote in message news That would explain my problems! Lots of good solutions there. The first suggestioon seems the most fitting for my purposes, since I only have 2 referenced workbooks. It would be easy enough just to have those two open, but its a pain when you forget or open the wrong one accidently. So, rather than mirror a whole load of sheets onto hidden sheets in my open file, i was wondering how easy it would to open the two referenced files automatically when I open the master file? (And close them automatically?) Could you do this with a macro? Not with macro, but you can do it p.e. in workbook's Open event: Open VBA editor (Alt+F11); In VBA Project window, right-click on ThisWorkbook beneath your project; Click on 'View Code' in dropdown menu; In left combo on top of code window, select 'Workbook'. A dummy Open event is created automatically - fill it with your code. About mirroring data - you don't need to mirror all sheets and columns - only those you need to refer to. The easiest way to create a mirror: Open the workbook, you want to link to. Activate target workbook; Insert an empty sheet (and name it); Into cell A1 on created sheet, enter the formula like =IF('[SourceWorkbook.xls]SourceSheet'!A1="","",'[SourceWorkbook.xls]SourceSheet'!A1) Copy the formula to range, including all wanted data (+ some amount of empty rows at bottom, when new data will be added into source workbook later); Delete all abundant columns (the ones you don't refer to) in mirrored table (NB! Delete entire columns - otherwise link formulas will be screwed); Close the source workbook. -- Arvi Laanemets ( My real mail address: arvilattarkon.ee ) "Arvi Laanemets" wrote: Hi INDIRECT doesn't work with closed workbooks. you have to use some different solution. a) When the number of workbooks you are linking to is limited, then you can mirror them on some hidden sheet. Now you refer to this/those hidden sheet(s) instead of files (I have a project where ~20 workbooks are linked to summary workbook in such a way). b) Some frequent resident of Excel NG's (I don't remember who) has an UDF or Add-In on his site to download, which is an equivalent for INDIRECT, but works with closed workbooks too. But when you want to design a workbook for use by several users, all of them must have it in their computers - which can be a problem. c) Design your workbook to link to some fixed workbook. When you want to swich the source workbook, you save it with this fixed name (not a good solution, but for some occassions will do). d) Create a procedure, which asks for workbook you want to link to, and rewrites then all links. You can start the procedure from hot key, or from button placed on worksheet. e) ... I'm sure there are other possible solutions, but for start it will do. -- Arvi Laanemets ( My real mail address: arvilattarkon.ee ) "rmellison" wrote in message ... I am using a text string to reference a block of cells in another worksheet. The text string is in a seperate cell, and I am using this string in a multitude of other cells using INDIRECT(TextStringCell). If the text string is in the form [Workbook]Worksheet!A1:B2, for example, i have to have the referenced workbook open for the link to work. I thought perhaps that if the string was in the form C:\Directory\[Workbook]Worksheet!A1:B2, i needn't have the referenced workbook open. Needless to say, this doesn't work. On other spreadsheets, where i do not call a reference indirectly, i can happily use the function ='C:\Directory\[Workbook]Worksheet!A1:B2 and I do not need to have the referenced workbook open. Is this problem just a quirk of an indirect reference, or is there a way around it? Perhaps I'm missing some apostrophes some where.... Thanks in advance. |
#9
|
|||
|
|||
Not proficient at VBA - the extent of my use of macros is the record/play
button on the macro toolbar! Will try the mirrored sheets. Thanks for your help. "Arvi Laanemets" wrote: Hi "rmellison" wrote in message news That would explain my problems! Lots of good solutions there. The first suggestioon seems the most fitting for my purposes, since I only have 2 referenced workbooks. It would be easy enough just to have those two open, but its a pain when you forget or open the wrong one accidently. So, rather than mirror a whole load of sheets onto hidden sheets in my open file, i was wondering how easy it would to open the two referenced files automatically when I open the master file? (And close them automatically?) Could you do this with a macro? Not with macro, but you can do it p.e. in workbook's Open event: Open VBA editor (Alt+F11); In VBA Project window, right-click on ThisWorkbook beneath your project; Click on 'View Code' in dropdown menu; In left combo on top of code window, select 'Workbook'. A dummy Open event is created automatically - fill it with your code. About mirroring data - you don't need to mirror all sheets and columns - only those you need to refer to. The easiest way to create a mirror: Open the workbook, you want to link to. Activate target workbook; Insert an empty sheet (and name it); Into cell A1 on created sheet, enter the formula like =IF('[SourceWorkbook.xls]SourceSheet'!A1="","",'[SourceWorkbook.xls]SourceSheet'!A1) Copy the formula to range, including all wanted data (+ some amount of empty rows at bottom, when new data will be added into source workbook later); Delete all abundant columns (the ones you don't refer to) in mirrored table (NB! Delete entire columns - otherwise link formulas will be screwed); Close the source workbook. -- Arvi Laanemets ( My real mail address: arvilattarkon.ee ) "Arvi Laanemets" wrote: Hi INDIRECT doesn't work with closed workbooks. you have to use some different solution. a) When the number of workbooks you are linking to is limited, then you can mirror them on some hidden sheet. Now you refer to this/those hidden sheet(s) instead of files (I have a project where ~20 workbooks are linked to summary workbook in such a way). b) Some frequent resident of Excel NG's (I don't remember who) has an UDF or Add-In on his site to download, which is an equivalent for INDIRECT, but works with closed workbooks too. But when you want to design a workbook for use by several users, all of them must have it in their computers - which can be a problem. c) Design your workbook to link to some fixed workbook. When you want to swich the source workbook, you save it with this fixed name (not a good solution, but for some occassions will do). d) Create a procedure, which asks for workbook you want to link to, and rewrites then all links. You can start the procedure from hot key, or from button placed on worksheet. e) ... I'm sure there are other possible solutions, but for start it will do. -- Arvi Laanemets ( My real mail address: arvilattarkon.ee ) "rmellison" wrote in message ... I am using a text string to reference a block of cells in another worksheet. The text string is in a seperate cell, and I am using this string in a multitude of other cells using INDIRECT(TextStringCell). If the text string is in the form [Workbook]Worksheet!A1:B2, for example, i have to have the referenced workbook open for the link to work. I thought perhaps that if the string was in the form C:\Directory\[Workbook]Worksheet!A1:B2, i needn't have the referenced workbook open. Needless to say, this doesn't work. On other spreadsheets, where i do not call a reference indirectly, i can happily use the function ='C:\Directory\[Workbook]Worksheet!A1:B2 and I do not need to have the referenced workbook open. Is this problem just a quirk of an indirect reference, or is there a way around it? Perhaps I'm missing some apostrophes some where.... Thanks in advance. |
#10
|
|||
|
|||
I just tried it and I see that the version on Harlan's site is not the
latest version. I attach the latest version here. You need to be very careful in defining the cell data, along the lines of 'C:\[workbook.xls]sheetname'!cell_ref be especially careful with the leading ', you will probably need two, '', as one just tells Excel it is text. '----- begin VBA ----- Function pull(xref As String) As Variant 'inspired by Bob Phillips and Laurent Longre 'but written by Harlan Grove '----------------------------------------------------------------- 'Copyright (c) 2003 Harlan Grove. ' 'This code is free software; you can redistribute it and/or modify 'it under the terms of the GNU General Public License as published 'by the Free Software Foundation; either version 2 of the License, 'or (at your option) any later version. '----------------------------------------------------------------- '2004-05-30 'still more fixes, this time to address apparent differences between 'XL8/97 and later versions. Specifically, fixed the InStrRev call, 'which is fubar in later versions and was using my own hacked version 'under XL8/97 which was using the wrong argument syntax. Also either 'XL8/97 didn't choke on CStr(pull) called when pull referred to an 'array while later versions do, or I never tested the 2004-03-25 fix 'against multiple cell references. '----------------------------------------------------------------- '2004-05-28 'fixed the previous fix - replaced all instances of 'expr' with 'xref' 'also now checking for initial single quote in xref, and if found 'advancing past it to get the full pathname [dumb, really dumb!] '----------------------------------------------------------------- '2004-03-25 'revised to check if filename in xref exists - if it does, proceed; 'otherwise, return a #REF! error immediately - this avoids Excel 'displaying dialogs when the referenced file doesn't exist '----------------------------------------------------------------- Dim xlapp As Object, xlwb As Workbook Dim b As String, r As Range, C As Range, n As Long '** begin 2004-05-30 changes ** '** begin 2004-05-28 changes ** '** begin 2004-03-25 changes ** n = InStrRev(xref, "\") If n 0 Then If Mid(xref, n, 2) = "\[" Then b = Left(xref, n) n = InStr(n + 2, xref, "]") - n - 2 If n 0 Then b = b & Mid(xref, Len(b) + 2, n) Else n = InStrRev(Len(xref), xref, "!") If n 0 Then b = Left(xref, n - 1) End If '** key 2004-05-28 addition ** If Left(b, 1) = "'" Then b = Mid(b, 2) On Error Resume Next If n 0 Then If Dir(b) = "" Then n = 0 Err.Clear On Error GoTo 0 End If If n = 0 Then pull = CVErr(xlErrRef) Exit Function End If '** end 2004-03-25 changes ** '** end 2004-05-28 changes ** pull = Evaluate(xref) '** key 2004-05-30 addition ** If IsArray(pull) Then Exit Function '** end 2004-05-30 changes ** If CStr(pull) = CStr(CVErr(xlErrRef)) Then On Error GoTo CleanUp 'immediate clean-up at this point Set xlapp = CreateObject("Excel.Application") Set xlwb = xlapp.Workbooks.Add 'needed by .ExecuteExcel4Macro On Error Resume Next 'now clean-up can wait n = InStr(InStr(1, xref, "]") + 1, xref, "!") b = Mid(xref, 1, n) Set r = xlwb.Sheets(1).Range(Mid(xref, n + 1)) If r Is Nothing Then pull = xlapp.ExecuteExcel4Macro(xref) Else For Each C In r C.Value = xlapp.ExecuteExcel4Macro(b & C.Address(1, 1, xlR1C1)) Next C pull = r.Value End If CleanUp: If Not xlwb Is Nothing Then xlwb.Close 0 If Not xlapp Is Nothing Then xlapp.Quit Set xlapp = Nothing End If End Function '----- end VBA ----- -- HTH Bob Phillips "rmellison" wrote in message ... My VBA knowledge is very limited (usually to the record/stop button on the macro toolbar). I have copied the code to a new module in my workbook, and the pull function appears in my UDF list. As a way of a test, I am just trying to reference one cell in a closed workbook using a string in A1, and =pull(a1) in another cell. Howewer, all i get is #value. Have tried with an open workbook, with and without the file path, with & without apostrophes.... Any suggestions? I get the feeling I'm over-complicating things now, but i've got this far.... "Bob Phillips" wrote: "Arvi Laanemets" wrote in message ... b) Some frequent resident of Excel NG's (I don't remember who) has an UDF or Add-In on his site to download, which is an equivalent for INDIRECT, but works with closed workbooks too. But when you want to design a workbook for use by several users, all of them must have it in their computers - which can be a problem. That would be Harlan Grove who wrote a UDF called PULL. You can find the function at his FTP site: ftp://members.aol.com/hrlngrv/ Look for pull.zip |
|
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Linking cells in a worksheet to other worksheets in a workbook | Dave | General Discussion | 4 | June 24th, 2005 06:18 PM |
Macro to search for and display data in another worksheet | Mark H | Worksheet Functions | 0 | June 14th, 2005 12:40 PM |
Summary worksheet referencing multiple worksheets | Jon | Worksheet Functions | 1 | January 27th, 2005 01:12 AM |
auto insert copy of worksheet | Bernie Deitrick | Worksheet Functions | 0 | March 4th, 2004 02:18 PM |
referencing a worksheet name in a cell | Stacey Snyder | Worksheet Functions | 3 | September 26th, 2003 12:35 AM |