Defined name reference in formula

pilot

Active Member
Joined
Feb 17, 2002
Messages
345
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 ?
 

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.

pilot

Active Member
Joined
Feb 17, 2002
Messages
345
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

Legend
Joined
Sep 3, 2002
Messages
10,473
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.
 

pilot

Active Member
Joined
Feb 17, 2002
Messages
345
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

MrExcel MVP
Joined
Feb 8, 2002
Messages
11,959

ADVERTISEMENT

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

pilot

Active Member
Joined
Feb 17, 2002
Messages
345
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()),"")
 

pilot

Active Member
Joined
Feb 17, 2002
Messages
345
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,129,553
Messages
5,636,989
Members
416,953
Latest member
broexc

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top