Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 - |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
INDIRECT Function | Excel Worksheet Functions | |||
Using INDIRECT Function and INDEX Function | Excel Discussion (Misc queries) | |||
INDIRECT function | Excel Discussion (Misc queries) | |||
INDIRECT function inside AND function | Excel Worksheet Functions | |||
Help with INDIRECT function. | Excel Worksheet Functions |