LinkBack URL
About LinkBacks
Hi All
I have used the function INDIRECT in 1 of my files.
The disadvantage is that both files (source and target) have to be open.
Is there a substitute for INDIRECT that works with a closed source file?
Thanks
Hein
Hi All
I have used the function INDIRECT in 1 of my files.
The disadvantage is that both files (source and target) have to be open.
Is there a substitute for INDIRECT that works with a closed source file?
Thanks
Hein
Last edited by Hein; 01-26-2009 at 05:18 AM.
You will need to use 3rd party Add-Ins, ie morefunc.xll -- see INDIRECT.EXT
My Recommended Reading:
Volatility
Sumproduct & Arrays
Pivot Intro
Email from XL - VBA & Outlook VBA
Function Dictionary & Function Translations
Dynamic Named Ranges
I'm not sure if I'm permitted to install this kind of 3rd party add-ins since I'm making my files for use elsewhere in the company but I will give it a go.
Thanks DonkeyOte
Hein
Think about restructuring your formulae to use offset/index/match etc. - there may be a workaround this way (without seeing data is hard to know...)
CC
CC, in this instance the use of INDIRECT is more to do with creating dynamic external links within other formulae.
That's fine - this:
=INDEX('C:\[Book1.xls]Sheet1'!$C$3:$F$11,2,2)
and this
=indirect("Book1.xls!"&"D4") (where D4 is assembled in some way)
achieve the same, but one works when book1 is closed - and, as it's not volatile, is more efficient.
Am I missing something?
CC
Will depend on the OP but I had perhaps foolishly made the assumption that INDIRECT was used for the purposes of creating the link... that is to say it is the file (path) itself (Book1.xls) that is dynamic.
I believe MoreFunc has the ability to embed itself in the workbook so it does not need to be separately installed as an add in -- correct, DO? (That may not change your IT departments's opinion, though).I'm not sure if I'm permitted to install this kind of 3rd party add-ins since I'm making my files for use elsewhere in the company but I will give it a go.
Entia non sunt multiplicanda sine necessitate
I believe there is an issue with 2007 but for other versions yes I believe so ... all very clever :-) ... but still need to install it as 3rd party initially which as shg says - IT may not be overly keen upon... but what they don't know...
I'm sure you have to do Trust access to VBA project for it to work -- and code that modifies code makes some virus scanners very nervous.
THe big question, as far as I can tell is the answer to this:
If that's not the case then you might well be able to use a workaround as I've alluded to.that is to say it is the file (path) itself (Book1.xls) that is dynamic.
CC
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks