Making part of a formula a variable

[Will Fleenor]

I want to be able to change a workbook's name in a formula of another
workbook using a variable.



For example, if in cell A7 there is a formula/reference as such:

=T:\Excel\'[Glroll01.xls]Receivables'!$B$5



I want the "01" of the workbook name "Glroll01.xls" to be a variable so that
as the workbook is renamed from month-to-month (e.g., Glroll05.xls), my
formula will change also when a trigger in another cell of the worksheet is
changed (e.g., if "05" is located in cell A1).that is, if I input "05" in
cell A1, the formula in cell A7 will change to "=T:\Excel\'[Glroll05.xls]Receivables'!$B$5"



I would prefer to do this using forumlas and not a macro.



Thanks in advance for your help



Thanks, Will

 

[Pecoflyer]

Try =indirect("T:\Excel\'[Glroll"&A1&".xls]Receivables'!$B$5")

I want to be able to change a workbook's name in a formula of another
workbook using a variable.



For example, if in cell A7 there is a formula/reference as such:

=T:\Excel\'[Glroll01.xls]Receivables'!$B$5



I want the "01" of the workbook name "Glroll01.xls" to be a variable s
that
as the workbook is renamed from month-to-month (e.g., Glroll05.xls)
my
formula will change also when a trigger in another cell of th
worksheet is
changed (e.g., if "05" is located in cell A1).that is, if I input "05
in
cell A1, the formula in cell A7 will change t
"=T:\Excel\'[Glroll05.xls]Receivables'!$B$5"



I would prefer to do this using forumlas and not a macro.



Thanks in advance for your help



Thanks, Wil

--
Pecoflye

Cheers -

'Firefox 3.5' (http://www.mozilla.com/en-US/firefox/all-beta.html
really IS fast

 

[Dave Peterson]

The function you'd want to use that's built into excel is =indirect(). But that
function returns an error if the sending workbook is closed.

If that's a problem, then Laurent Longre has an addin (morefunc.xll) at:
http://xcell05.free.fr/
or
http://xcell05.free.fr/morefunc/english/index.htm

That includes =indirect.ext() that may help you.

I want to be able to change a workbook's name in a formula of another
workbook using a variable.

For example, if in cell A7 there is a formula/reference as such:

=T:\Excel\'[Glroll01.xls]Receivables'!$B$5

I want the "01" of the workbook name "Glroll01.xls" to be a variable so that
as the workbook is renamed from month-to-month (e.g., Glroll05.xls), my
formula will change also when a trigger in another cell of the worksheet is
changed (e.g., if "05" is located in cell A1).that is, if I input "05" in
cell A1, the formula in cell A7 will change to "=T:\Excel\'[Glroll05.xls]Receivables'!$B$5"

I would prefer to do this using forumlas and not a macro.

Thanks in advance for your help

Thanks, Will