Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
variable formula | Excel Worksheet Functions | |||
Variable VBA Max Formula | Excel Discussion (Misc queries) | |||
DDE formula with variable | Excel Worksheet Functions | |||
Using a second variable within a formula... | Excel Discussion (Misc queries) | |||
Variable to formula. | Excel Programming |