Help with Index/Match function or similar

[exoticdisease]

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

 

[Stefi]

Try this:
=INDEX(INDIRECT(A1),1,1)
Regards,
Stefi

„exoticdisease†ezt írta:

 

[exoticdisease]

I've read the sumproduct explanation, but all that does is multiply values by
other values...ingeniously converting text to boolean number values I admit,
but that's not much good for my problem, unless I'm missing something? Could
you give me an example formula?

You could use SUMPRODUCT(--(TEXT( a good explanation can be found here
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

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)

 

[exoticdisease]

The indirect function was pretty close and it started giving me values that I
wanted, but then bloody excel removed the "'" (apostrophe) character from the
beginning of my text string which is to be used as a lookup function and it
started giving me #REFs! Is there any way to stop excel removing the
apostrophe symbol when it's the first character of a text string?

 

[Stefi]

I guess the problem is that apostrophe is NOT the first character of the
string but a sign for Excel to handle cell content as text despite cell
format is NOT text.
Maybe you could try to store stings in text formatted cells (in this way you
can spare apostrophes), or to use TEXT(cellref),"@") instead of plain cellref
in Lookups if you search text strings.
Note! If you change cell format to text, it won't convert existing cell
content to string, you have to convert it with Text function and copy back in
the cell reformatted like text.

Give examples if problem is still not solved, but I come back to them only
Monday, I finished for today:

Stefi


„exoticdisease†ezt írta: