Making part of a formula a variable
 

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

Making part of a formula a variable
 

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



Will Fleenor;452713 Wrote:
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

Cheers -

'Firefox 3.5' (http://www.mozilla.com/en-US/firefox/all-beta.html)
really IS fast !
------------------------------------------------------------------------
Pecoflyer's Profile: http://www.thecodecage.com/forumz/member.php?userid=14
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=125308

Making part of a formula a variable
 

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.



Will Fleenor wrote:

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

--

Dave Peterson