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

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

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

.

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 -