INDEX/MATCH update limitation

[mariekek5]

I was very happy when I found the INDEX/MATCH formula. However, as MATCH and
VLOOKUP formulas seem to be able to udate themselves, even though the source
document is closed...INDEX/MATCH formulas seem not be able to update when the
source document is closed.

I am using Microsoft Excel 2003.

Am i doing something wrong, or is it simply impossible?

 

[Shane Devenshire]

Hi,

The problem is with INDEX - in Excel it doesn't work with external files
that are closed.

It works with closed files in Lotus 1-2-3 even back in 1982. And in Open
Office today.

 

[T. Valko]

The problem is with INDEX - in Excel it doesn't

work with external files that are closed.

Sure it does.

=INDEX('C:\TV\[X.xls]Sheet1'!$D:$D,MATCH("junk",'C:\TV\[X.xls]Sheet1'!$C:$C,0))

Works just fine when the file X.xls is closed.

 

[Shane Devenshire]

Oops, read too fast, thought we were talking about INDIRECT. My comments
apply to INDIRECT, not INDEX.

--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire

The problem is with INDEX - in Excel it doesn't
work with external files that are closed.

Sure it does.

=INDEX('C:\TV\[X.xls]Sheet1'!$D:$D,MATCH("junk",'C:\TV\[X.xls]Sheet1'!$C:$C,0))

Works just fine when the file X.xls is closed.


--
Biff
Microsoft Excel MVP

Hi,

The problem is with INDEX - in Excel it doesn't work with external files
that are closed.

It works with closed files in Lotus 1-2-3 even back in 1982. And in Open
Office today.

--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire