Formula Variable
 

Is there any way to use a variable in a formula? I have
two workbooks that I need to add together each month.
Rather than go in and use find replace each month to
change the formula I would like the formula to
automatically do it. Example - two workbooks WB1 and
WB2, formula for this month = +A1+'[WB2.xls]A1. Next
month WB2 will be WB3, so I need a formula in WB1 that
will be "+A1+'[WB3.xls]A1" . In WB1 cell A2 I have the
corresponding month so in month 3 it would be 3 etc. So
the formula that I am trying to create is "=A1+(WB
&"A2".xls]A1". Obviously it does not work.

Any ideas would be very much appreciated.

Formula Variable
 

Hi
if the other workbook is OPEN try
=A1+INDIRECT("'[WB" & A2 & ".xls]sheetname'!A1")
i A2 stores your 'variable'. You have to adapt the worksheetname

--
Regards
Frank Kabel
Frankfurt, Germany
"Ronbo" schrieb im Newsbeitrag
...
Is there any way to use a variable in a formula? I have
two workbooks that I need to add together each month.
Rather than go in and use find replace each month to
change the formula I would like the formula to
automatically do it. Example - two workbooks WB1 and
WB2, formula for this month = +A1+'[WB2.xls]A1. Next
month WB2 will be WB3, so I need a formula in WB1 that
will be "+A1+'[WB3.xls]A1" . In WB1 cell A2 I have the
corresponding month so in month 3 it would be 3 etc. So
the formula that I am trying to create is "=A1+(WB
&"A2".xls]A1". Obviously it does not work.

Any ideas would be very much appreciated.

Formula Variable
 

-----Original Message-----
Hi
if the other workbook is OPEN try
=A1+INDIRECT("'[WB" & A2 & ".xls]sheetname'!A1")
i A2 stores your 'variable'. You have to adapt the
worksheetname

--
Regards
Frank Kabel
Frankfurt, Germany
"Ronbo" schrieb im
Newsbeitrag
...
Is there any way to use a variable in a formula? I
have
two workbooks that I need to add together each month.
Rather than go in and use find replace each month to
change the formula I would like the formula to
automatically do it. Example - two workbooks WB1 and
WB2, formula for this month = +A1+'[WB2.xls]A1. Next
month WB2 will be WB3, so I need a formula in WB1 that
will be "+A1+'[WB3.xls]A1" . In WB1 cell A2 I have the
corresponding month so in month 3 it would be 3 etc.
So
the formula that I am trying to create is "=A1+(WB
&"A2".xls]A1". Obviously it does not work.

Any ideas would be very much appreciated.


.
Thanks for the info, however the worksheet is not open.
Any other Ideas?

Formula Variable
 

Hi
If the other workbook is closed try the following:
have a look at the Add-In MOREFUNC.XLL
(http://longre.free.fr/english)
use the function INDIRECT.EXT.e.g.
=INDIRECT.EXT("''C:\Documents and Settings\" & cell_ref1 &
"\MyDocuments\My folder\[" & cell_ref2 & cell_ref3 & ".xls]Expense
Statement'!$F$2")
thats is just replace your function INDIRECT with INDIRECT.EXT and add
the path information

you may also have a look at the following thread (describing further
alternatives for accessing closed workbooks): http://tinyurl.com/2c62u

--
Regards
Frank Kabel
Frankfurt, Germany
"Ronbo" schrieb im Newsbeitrag
...

-----Original Message-----
Hi
if the other workbook is OPEN try
=A1+INDIRECT("'[WB" & A2 & ".xls]sheetname'!A1")
i A2 stores your 'variable'. You have to adapt the
worksheetname

--
Regards
Frank Kabel
Frankfurt, Germany
"Ronbo" schrieb im
Newsbeitrag
...
Is there any way to use a variable in a formula? I
have
two workbooks that I need to add together each month.
Rather than go in and use find replace each month to
change the formula I would like the formula to
automatically do it. Example - two workbooks WB1 and
WB2, formula for this month = +A1+'[WB2.xls]A1. Next
month WB2 will be WB3, so I need a formula in WB1 that
will be "+A1+'[WB3.xls]A1" . In WB1 cell A2 I have the
corresponding month so in month 3 it would be 3 etc.
So
the formula that I am trying to create is "=A1+(WB
&"A2".xls]A1". Obviously it does not work.

Any ideas would be very much appreciated.


.
Thanks for the info, however the worksheet is not open.
Any other Ideas?