Index function with quotes and '&' sign in reference
 

I am trying to use a changing reference inside an 'index' function,
however I keep getting a '#value' error. In trying to troubleshoot, I
simplified the formula down to this:

=INDEX('Jan 07'!$C$22&":$C$24",1)

I still get the '#value' error. Ultimately, I would like to do this:

=INDEX("'X:\Path\[Filename.xls]worksheettab'!
$"&substitute(address(1,10,4),"1","")&"$527"&":$J$ 563",1)

But I think I need to take baby steps. :) The 'substitute(address)'
part of the formula is just to get a column letter to use with the
number $527 to create a cell reference. If there is a simpler way to
do this don't hestitate to tell me. :)

Thanks!

Index function with quotes and '&' sign in reference
 

On Feb 15, 1:37 pm, wrote:
I am trying to use a changing reference inside an 'index' function,
however I keep getting a '#value' error. In trying to troubleshoot, I
simplified the formula down to this:

=INDEX('Jan 07'!$C$22&":$C$24",1)

I still get the '#value' error. Ultimately, I would like to do this:

=INDEX("'X:\Path\[Filename.xls]worksheettab'!
$"&substitute(address(1,10,4),"1","")&"$527"&":$J$ 563",1)

But I think I need to take baby steps. :) The 'substitute(address)'
part of the formula is just to get a column letter to use with the
number $527 to create a cell reference. If there is a simpler way to
do this don't hestitate to tell me. :)

Thanks!

Oh and I forgot to mention that I am trying to avoid using the
'indirect' function.