SUBJECT: There is now a way to fetch information on closed external
workbooks from "named" worksheets!!!
-------------------------------------------------------------------------------------
Ok, I managed to figure out how to do this (took an afternoon!) I
found my answer by combing old posts around the web, so in the
interest of helping a future 'me' seeking a similar solution, here is
what I found:
--------------------------------------------------
The main issue with Excel's built-in "Indirect" function as suggested
above is that it can't access closed workbooks - here is a blog post
on 3 methods to combat this:
http://www.dicks-blog.com/archives/2...sed-workbooks/
Of these methods that can access closed workbooks, I first tried out
the MOREFUNC method and it did not work for me because I have named
worksheets (e.g. instead of "Sheet1" I use "Summary")
Then I tried Harlan Grove's function, but again - an issue with named
worksheets.
Finally, I found a modification to Grove's method that works perfectly
for me, plenty of rejoicing and shouting in this office today!!!
1. Go here to view the post
http://groups.google.ca/group/micros...b861364e0e852a
(Or, a direct download of the Function coding:
http://www3.sympatico.ca/sstackho/LinkedRange.zip )
2. Then use the instructions on the following page to install
http://help.lockergnome.com/office/H...ict945851.html
The page is for the Grove version of the code, but the instructions
are nearly identical - once the LinkedRange function is installed,
however, just be sure to use "LinkedRange" instead of "Pull" in your
formulas
So instead of using this sample code (from the Grove help page):
=PULL("'"&"D:\test\"&"["&"foo.xls"&"]"&"Sheet1"&"'!"&"C5")
It would become:
=LINKEDRANGE("'"&"D:\test\"&"["&"foo.xls"&"]"&"Sheet1"&"'!"&"C5")
Hope that helps! Thanks to Harlan Grove and Shawn Stackhouse for
their respective contributions to this joyous solution. (It has
certainly made my day, after hours of hunting I have a solution to
sort my data!) Combining their instructions and code, there is now a
way to fetch information on closed external workbooks from "named"
worksheets!!!
Sincerely,
Disneyandbond
------------------------------------------------------
P.S.
Here's the easiest way to install the LinkedRange function -
-to start: download the ZIP file from the site mentioned above (http://
www3.sympatico.ca/sstackho/LinkedRange.zip)
-Then, extract the .BAS file to somewhere easy to find (e.g. your
Desktop)
-Then fire up excel, press ALT + F11 to bring up the Visual Basic menu
-Go to File -- "Import File", select the .BAS file wherever you saved
it, then hit ok
-There should now be a module called "modLinkedRange" in your Modules
-Hit ALT + F11 again, you'll now be back in Excel and ready to use the
LINKEDRANGE command in your formulas!!!