INDEX returns #N/A for text?

[TonyK]

Just a quick prob someone might be able to help with. I've got a master file
linked to numerous subfiles which extracts data to form a matrix.
Essentially it looks for a matching date in the subfile and returns the
appropriate entry using:

=INDEX('Z:\[Bath.xls]Availability'!$B$13:$V$13,1,MATCH(B3,'Z:\[Bath.xls]Avai
lability'!$B$10:$V$10))

This works fine as long as the entries in the subfile are numeric values. If
they are changed to text the formula returns #N/A although if both files are
open and you change the data "live" its okay, you only seem to get the error
when the subfile is closed.

Any help appreciated!

Tony

 

[TonyK]

Just a quick prob someone might be able to help with. I've got a master file
linked to numerous subfiles which extracts data to form a matrix.
Essentially it looks for a matching date in the subfile and returns the
appropriate entry using:

=INDEX('Z:\[Bath.xls]Availability'!$B$13:$V$13,1,MATCH(B3,'Z:\[Bath.xls]Avai
lability'!$B$10:$V$10))

This works fine as long as the entries in the subfile are numeric values. If
they are changed to text the formula returns #N/A although if both files are
open and you change the data "live" its okay, you only seem to get the error
when the subfile is closed.

Any help appreciated!

Tony

Sorry, Excel 2000 under Win2K