If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Indirect function
I am trying to get the following to work to calculate percent change:
=INDIRECT(("'"&$E$1&"'!" &$C$2& "42" - "'"&$E$1&"'!" &$D$2& "42")/"'"&$E$1&"'!" &$D$2& "42") whe $E$1 holds the sheet name(in same workbook) containing the data and $C$2 and $D$2 hold the row references such that without the Indirect it would read: =('Data'!X43-'Data'!W43)/('Data'!W43) I need the Indirect becuase I have 35 cases in which I need to do this calculation and if all I have to do is change the row reference I am golden! Any help is appreciated. Thanks! -- javablood |
#2
|
|||
|
|||
Indirect function
You've basically got to have your INDIRECT three times in the formula,
once for each term - the only arithmetic you can do within the INDIRECT function is that which affects the cell reference (eg adding onto the row). So, try this: =(INDIRECT("'"&$E$1&"'!"&$C$2&"42") - INDIRECT("'"&$E$1&"'!"&$D $2&"42")) / INDIRECT("'"&$E$1&"'!"&$D$2&"42") Hope this helps. Pete On Feb 11, 2:14*pm, javablood wrote: I am trying to get the following to work to calculate percent change: =INDIRECT(("'"&$E$1&"'!" &$C$2& "42" - "'"&$E$1&"'!" &$D$2& "42")/"'"&$E$1&"'!" &$D$2& "42") whe $E$1 holds the sheet name(in same workbook) containing the data and $C$2 and $D$2 hold the row references such that without the Indirect it would read: =('Data'!X43-'Data'!W43)/('Data'!W43) I need the Indirect becuase I have 35 cases in which I need to do this calculation and if all I have to do is change the row reference I am golden! Any help is appreciated. *Thanks! -- javablood |
#3
|
|||
|
|||
Indirect function
Thanks Pete! It works!
-- javablood "Pete_UK" wrote: You've basically got to have your INDIRECT three times in the formula, once for each term - the only arithmetic you can do within the INDIRECT function is that which affects the cell reference (eg adding onto the row). So, try this: =(INDIRECT("'"&$E$1&"'!"&$C$2&"42") - INDIRECT("'"&$E$1&"'!"&$D $2&"42")) / INDIRECT("'"&$E$1&"'!"&$D$2&"42") Hope this helps. Pete On Feb 11, 2:14 pm, javablood wrote: I am trying to get the following to work to calculate percent change: =INDIRECT(("'"&$E$1&"'!" &$C$2& "42" - "'"&$E$1&"'!" &$D$2& "42")/"'"&$E$1&"'!" &$D$2& "42") whe $E$1 holds the sheet name(in same workbook) containing the data and $C$2 and $D$2 hold the row references such that without the Indirect it would read: =('Data'!X43-'Data'!W43)/('Data'!W43) I need the Indirect becuase I have 35 cases in which I need to do this calculation and if all I have to do is change the row reference I am golden! Any help is appreciated. Thanks! -- javablood . |
#4
|
|||
|
|||
Indirect function
You're welcome - thanks for feeding back.
Pete On Feb 11, 5:17*pm, javablood wrote: Thanks Pete! *It works! -- javablood "Pete_UK" wrote: You've basically got to have your INDIRECT three times in the formula, once for each term - the only arithmetic you can do within the INDIRECT function is that which affects the cell reference (eg adding onto the row). So, try this: =(INDIRECT("'"&$E$1&"'!"&$C$2&"42") - INDIRECT("'"&$E$1&"'!"&$D $2&"42")) / INDIRECT("'"&$E$1&"'!"&$D$2&"42") Hope this helps. Pete On Feb 11, 2:14 pm, javablood wrote: I am trying to get the following to work to calculate percent change: =INDIRECT(("'"&$E$1&"'!" &$C$2& "42" - "'"&$E$1&"'!" &$D$2& "42")/"'"&$E$1&"'!" &$D$2& "42") whe $E$1 holds the sheet name(in same workbook) containing the data and $C$2 and $D$2 hold the row references such that without the Indirect it would read: =('Data'!X43-'Data'!W43)/('Data'!W43) I need the Indirect becuase I have 35 cases in which I need to do this calculation and if all I have to do is change the row reference I am golden! Any help is appreciated. *Thanks! -- javablood .- Hide quoted text - - Show quoted text - |
Thread Tools | |
Display Modes | |
|
|