Indirect function

[javablood]

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")

where: $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!

 

[John Bundy]

You have to put the cell reference in with the sheet in your indirect, i have
my sheetname in B1
=((INDIRECT(B1&"!X43")-INDIRECT(B1&"!W43"))/INDIRECT(B1&"!W43"))

 

[Pete_UK]

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

 

[javablood]

John,

Thank you. It worked! I was going batty! I did not know I needed all
those INDIRECT references. When I evaluated the formula it looked like this:

= Indirect("('Data'!X43-'Data'!W43)/Data'!$W43")

and I did not know why I had the " at the ends because everything else
looked fine. Now I know!?

 

[javablood]

Thanks Pete! It works!
--
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



.

 

[Pete_UK]

You're welcome - thanks for feeding back.

Pete

Thanks Pete!  It works!
--
javablood








- Show quoted text -