I would like to write an index function that takes its array from text
written in a1 in my spreadsheet. in a1 there is written a formula that
results in: [M10 Liver Tx Paeds Leeds.xls]Jan'!$1:$65536, which is a
reference to another spreadsheet stored on the server. I want to write my
index function using this as the array, as follows:

=index(left(a1,100),1,1)

The left(a1,100) is just the best attempt I've had of getting it to print
the data that will be used as the array...it didn't work though.

Every time I try this I get a null value, and no matter what combination of
substitute, left, right, text...etc I use I can't get it just to print the
text as if I'd copied and pasted it...any ideas would really help