Changing a file name in a formula with data validation

Have a look at this very similar thread from earlier today.

http://www.excelforum.com/excel-prog...to-a-path.html

I am attempting to copy your instructions from the other tread, but am getting a #REF! error.

Show us your formula AND the correct full path to the file.

I'm having another issue...
I'm trying to compare 2011 and 2010 data on the same sheet, but when I copy the formula to an adjacent cell, changing only the year of the file path from:

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

and I close the original 2011 file and open the 2010 file, the 2011 numbers revert to 0's while the 2010 numbers calculate to the correct values.

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.