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. :) Also, I am trying to avoid
using the 'indirect' function.


Thanks!