Hi!
I've been using the INDIRECT() function for various files that follow a pattern with their filenames, but the thing is I would need to keep them all open for them to display the correct values and I really can't do that.
I was wondering if there was some way I could get around that or if I was stuck with opening them all every single time.
Thanks in advance!
Last edited by Jumbala; 03-25-2010 at 03:26 PM.
Regads your general options see: http://www.dailydoseofexcel.com/arch...sed-workbooks/
This topic has been covered numerous times before and I'm afraid I've nothing new to add - if you search the board for INDIRECT.EXT, Harlan Grove's PULL function etc you will find other threads which have more detail.
My Recommended Reading:
Volatility
Sumproduct & Arrays
Pivot Intro
Email from XL - VBA & Outlook VBA
Function Dictionary & Function Translations
Dynamic Named Ranges
Thanks for trying to help, DonkeyOte... I tried everything I could using INDIRECT.EXT, but it doesn't seem to be working when the workbook is closed... It works when it's open, though.
Basically, that means I'm stuck with exactly the same problem.
Any idea why? The error the function is returning is a #REF error.
Hi,
Have you downloaded and installed the MoreFunc add-in from Laurent' s site
http://xcell05.free.fr/english/index.html
The design for Indirect.ext is indeed to work for closed workbooks ...
HTH
Yes, I downloaded and installed it... I trying substituting one of my INDIRECT functions with INDIRECT.EXT and it only works when the other workbook is open, which proves it is installed right (I guess?).
Unless I'm using it wrong and there are some parameters I need to add or something, I can't see why it's working with open workbooks but not with closed workbooks.
I'm going to try to explain what I think the error might be since Google isn't being too helpful with my problem...
I was using the regular INDIRECT formula so I could open different workbooks that were following a given pattern (Week 1.xml, Week 2.xml, etc.)
So the regular INDIRECT formula to get the cells in the range $A$3:$J$41 in the worksheet "Table x" in the workbook "Table y"
So I read on some sites that you have to use the absolute path instead of the relative path to have INDIRECT.EXT work with closed workbooks... I tried
Please Login or Register to view this content.
If the problem really is the absolute path thing, what is wrong with my code?Please Login or Register to view this content.
Thanks a lot in advance for the replies!
You are absolutely right ...
The very exact complete path has to be used for the function to work ...
HTH
All right... So how do I write it to make it work with the formula I'm using? Like in my last post?
Are there some misplaced apostrophes or something?
An atttempt ...
because I cannot guess about the blanks in the file name ... about the sheet name in the file ...
HTHPlease Login or Register to view this content.
Last edited by JeanRage; 03-25-2010 at 02:53 AM.
I absolutely don't get it... With the full path, I can make it work with the INDIRECT.EXT function as long as the workbook is open, but as soon as a try with it closed, I get that #REF error again....
Here's the example I was trying:
Which would translate to:Please Login or Register to view this content.
Can anyone see why it won't work with closed workbooks? Is it about the apostrophes? (')Please Login or Register to view this content.
I really can't seem to find what's wrong with my code...
I found a problem with my function... It seems like the INDIRECT.EXT function doesn't work with xml files... So I just saved my workbooks as .xls and it solved the problem for the formulas that didn't use VLOOKUP
For those that do, though, I get a #N/A error, but when the workbook is open, it works flawlessly...
Any help?
Which translates to:Please Login or Register to view this content.
Please Login or Register to view this content.
Last edited by Jumbala; 03-25-2010 at 09:57 AM.
Oops ...Sorry I did not notice your file extension ... thought you were dealing with xls ...
No problem at all! But would you know why it's not working with the VLOOKUP function as shown in my last post?Originally Posted by JeanRage
Have a try with
You might need to use Harlan Grove's Pull function for the Vlookup() ...Please Login or Register to view this content.
Last edited by JeanRage; 03-25-2010 at 10:41 AM.
For your information, a good recap is available ...
http://www.dailydoseofexcel.com/arch...sed-workbooks/
HTH
I had read on Harlan Grove's Pull function and tried finding it on the internet but it seems like all the links to download it are down (or the link to the code in the link you gave me gives a "temporary server error" that has been lasting since I first went to that link when it was posted earlier in this thread), do you know of a place where I could get it?Have a try with
You might need to use Harlan Grove's Pull function for the Vlookup() ...Please Login or Register to view this content.
Last edited by Jumbala; 03-25-2010 at 11:13 AM.
Okay, seems like I have found the code somewhere, but I have no idea how to "install" it.
As far as I can remember, Harlan' s code is a function ...
So you just need to copy the code into a standard module ...
and use in your sheet:
HTHPlease Login or Register to view this content.
Thanks a lot JeanRage, it worked flawlessly!
I really appreciate your help and patience!
Glad you have managed to fix your problem ...
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks