Changing a file name in a formula with data validation

The file has also become extremely slow. Is this a product of using the INDIRECT.EXT function? And is there a solution?

Sorry for late response - busy with other things yesterday.

I'm afraid I don't have a definitive answer for you with regard to the formula producing 0's when the source file is closed. But I would suggest you have a look at the link for the INDIRECT.EXT() again (http://xcell05.free.fr/morefunc/engl...direct.ext.htm) and specifically the section "MODE" Argument as well as the Remarks that immediately follow.

As for slowing things down - INDIRECT() is one of the volatile functions (see here for others: http://www.decisionmodels.com/calcsecretsi.htm) so it will slow things down if there are a lot of volatile functions in play but I wouldn't expect a couple to have much of an impact. Do you have any others?

Try putting a trigger on the formulas to test:

=IF(AA1=1,INDIRECT.EXT("'D:\Projects\2011\East\[Sales_"&Criteria!$A$1&"-Washington.xls]Area2'!$O$25"),"")

Change the AA1 cell to any other cell not currently in use and place a 1 in the cell to have the calculations take place and remove the 1 when you don't need the calculations to happen.
I've used that method with a bunch of SUMPRODUCT()'s in order to control when they calculate.

Hey Cutter,

After reading this thread, I have a similar problem, I want to do the same thing as acellis9; however, I want to add a VLOOKUP to find an account value in the file I am looking for. Is there a way to do this?

Thus the formula I have now (without your advice above about INDIRECT) =

=VLOOKUP(E15,'[TB-SMID 7.11.xls]Sheet3'!$A$1:$N$27,12,FALSE)

E15 is a Account Number from the Trial Balance that the formula searches for in the TB it is looking up. I would like to make the "7.11" part of the formula a cell that can easily be changed. Any help would be great, thanks so much.