Making part of a formula a variable


W

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
 
Ad

Advertisements

D

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.



Will said:
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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top