Thanks, that works great but brings a follow-on question. How expensive is this type formula in terms of processing power? I would like to use this trick several thousand times in my wb. Am I correct in saying it is a volatile function that gets recalculated every time a recalc runs whether or not this cell was changed?
The task at hand is I'm creating a summary wb for use with about 20 branches (each branch has its own summary wb) and I'm trying to write the formulas generically rather than having to modify each wb with many find&replace actions. From my earlier posts, you can see the branch number is part of the formula. I'd like to define the branch number as a constant (Defined Name) that can be changed once when modifying the summary wb to work for another branch. The INDIRECT function works with the formula posted earlier but will it work in the following formula which references other files in the same path but they are not open?
The INDIRECT.EXT definitely sounds like the best way to go, but…
My IT Department has an antiquated way of thinking and is extremely protective over their realm. They are unwilling to install the Morefunc add-in without extensive testing and won't even consider starting the process until 6 months from now, so…
Is there a comparable UDF or other method that might accomplish the same thing? I realize this would reinvent the wheel but this is my only option right now. The basic problem is I need to access closed files whose filepath includes characters from a defined name.
It would look something like this, where "23" is a defined name that may change and (10-03 Sales logs.xls) is not open:
Sorry, but I'm not sure what you mean by using VBA. There are thousands of formulas similar to above in the wb. Are you suggesting using VBA to update the formulas throughout the wb? I was hoping someone would know how to design a UDF that worked like INDIRECT.EXT.