Alternative for INDIRECT

[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

[DonkeyOte]

You will need to use 3rd party Add-Ins, ie morefunc.xll -- see INDIRECT.EXT

[Hein]

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

[Cheeky Charlie]

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

[DonkeyOte]

CC, in this instance the use of INDIRECT is more to do with creating dynamic external links within other formulae.

[Cheeky Charlie]

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

[DonkeyOte]

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.

[shg]

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.

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).

[DonkeyOte]

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...

[shg]

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.

[Hein]

Sorry for not responding sooner, but I thought that this thread was over/closed.

One of the reasons why I'm not happy to use 3rd party add-ins is that our IT dept may not approve.
The other is that the file will be embedded in SAP and I'm not sure what's going to happen if I change/add it.

I have found a way to keep the file(-s) usable.
I will dig into your sugegstions as a learning experience and use it in other places (eg private)

Thanks for all your suggestions and contributions

Hein

[Cheeky Charlie]

THe big question, as far as I can tell is the answer to this:

that is to say it is the file (path) itself (Book1.xls) that is dynamic.

If that's not the case then you might well be able to use a workaround as I've alluded to.
CC