Defined name reference in formula

[pilot]

My formula currently reads =Br23Details!B1

A defined name for BrNo is ="23"

How would I rewrite the formula to be more like =Br&BrNo&Details!B1 ?

 

[Juan Pablo González]

=INDIRECT("Br" & BrNo & "Details!B1")

 

[pilot]

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?

 

[just_jon]

It is both expensive and volatile, and that particular flavor works only with open workbooks.

EDIT -- What is the task at hand; perhaps there's a better way.

 

[Juan Pablo González]

Maybe this works for you too:

http://www.mrexcel.com/board2/viewtopic.php?t=74234

 

[pilot]

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?

=IF(FileExists("P:\Personal\Perf plan\09-03 Sales logs.xls"),IF(ISERROR(V('P:\Personal\Perf plan\[09-03 Sales logs.xls]Totals'!$C$2)),"",V()),"")

The above path is what I use now but I'm changing to something like
G:\Zones\Br23\09-03 Sales logs.xls
if the INDIRECT function ((or something better from this board)) will work in the path.

 

[Juan Pablo González]

If the workbook is closed, try using INDIRECT.EXT, a function available with the MoreFunc.xll Add In.

 

[pilot]

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:

=IF(FileExists("G:\Zones\Br23\10-03 Sales logs.xls"),IF(ISERROR(V('G:\Zones\Br23\[10-03 Sales logs.xls]Totals'!C$14)),"",V()),"")

 

[just_jon]

Might be a case of better off using VBA?

 

[pilot]

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.