You could use SUMPRODUCT(--(TEXT( a good explanation can be found here
http://www.xldynamic.com/source/xld.SUMPRODUCT.html
exoticdisease;268310 Wrote:
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


--
Simon Lloyd

Regards,
Simon Lloyd
'The Code Cage' (http://www.thecodecage.com)
------------------------------------------------------------------------
Simon Lloyd's Profile: http://www.thecodecage.com/forumz/member.php?userid=1
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=74867