Stop INDIRECT.EXT from updating all the time

[pwebley]

I have a workbook using the INDIRECT.EXT function referencing different workbooks in the same file. It is pulling data from them into a summary sheet correctly, however, it does it after every cell that gets edited. The update takes about 15 seconds and is very annoying. Is there a way I can command it when to update and not to otherwise? Any help will be appreciated.

 

[mole999]

change to false

http://xcell05.free.fr/morefunc/english/indirect.ext.htm

 

[pwebley]

This is what I have:

=IF(ISERROR((INDIRECT.EXT(CONCATENATE($E3,G$1)))),"",(INDIRECT.EXT(CONCATENATE($E3,G$1))))

Where would I put the false?

 

[pwebley]

This is what I have now:

=IF(ISERROR((INDIRECT.EXT(CONCATENATE($E3,F$1),FALSE))),"",(INDIRECT.EXT(CONCATENATE($E3,F$1),FALSE)))

It is working a lot faster now.

 

[pwebley]

Everything works great now, thanks!

 

[Aladin Akyurek]

This is what I have now:

=IF(ISERROR((INDIRECT.EXT(CONCATENATE($E3,F$1),FALSE))),"",(INDIRECT.EXT(CONCATENATE($E3,F$1),FALSE)))

It is working a lot faster now.

What kind of value does the INDIRECT.EXT bit return?

 

[pwebley]

It returns a text value.

 

[Aladin Akyurek]

It returns a text value.

Probably less costly...

=LOOKUP(REPT("z",255),CHOOSE({1,2},"",INDIRECT.EXT($E3&F$1,FALSE)))

 

[pwebley]

Amazing. I see that it works but I don't know why it works. Why is there a repeat function and a choose function? The logic is escaping me.

 

[Aladin Akyurek]

Amazing. I see that it works but I don't know why it works. Why is there a repeat function and a choose function? The logic is escaping me.

REPT("z",255) yields a string of 255 z's, which is lexically a big string (BigStr).

LOOKUP(BigStr,Reference) returns the last text value from Reference. How that happens is described in:

http://www.mrexcel.com/forum/showthread.php?t=310278 (post #7)

which treats LOOKUP(BigNum,Reference), a similar construct.

CHOOSE, as used here, builds a reference (an array) consisting of two text items: "" and the result from INDIRECT.EXT. This reference, fed to LOOKUP gives us the desired result like in:

{"","FAD"} ==> FAD

{"",""} ==> ""

{"",#REF!} ==> ""