Index function with quotes and '&' sign in reference section

[bird_222]

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!

 

[Tom Ogilvy]

Linking to closed workbooks isn't supported - so you can't dynamically build
your reference. If you want something like this, use the change event or
the calculate event to hard code the formula in the cell (building the
appropriate link).


If the workbook is open, then you can use the Indirect function to build the
reference.

 

[Bob Phillips]

So what does 'Jan 07'!$C$22 contain.

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)