Dynamic Charts
 

Can anyone explain the following formula to me:

=Data!$A$5:INDEX(Data!$A:$A,MATCH(9.99999E+307,Dat a!$A:$A))

I am setting up a dynamic chart and found help on a website, I have used the
formula (displayed below) as a dynamic range formula for AllDates that
expands when more data is added to the column. The insructions stated that in
the Define Names dialog box, type AllDates in the Name Box, and enter this
formula in the Refers To box:

=Data!$A$5:INDEX(Data!$A:$A,MATCH(9.99999E+307,Dat a!$A:$A))

The formula does work but I don't understand it, can anyone explain the
formula to me?
--
Many Thanks
Cath

Dynamic Charts
 

MATCH(9.99999E+307,Data!$A:$A)

finds the row of the last cell in column A that contains a numeric value.

INDEX(Data!$A:$A,MATCH(9.99999E+307,Data!$A:$A))

is the cell in column A, row from the MATCH function.

Data!$A$5:INDEX(Data!$A:$A,MATCH(9.99999E+307,Data !$A:$A))

is the range starting at A5 and ending at the cell defined above.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______



"Cath" wrote in message
...
Can anyone explain the following formula to me:

=Data!$A$5:INDEX(Data!$A:$A,MATCH(9.99999E+307,Dat a!$A:$A))

I am setting up a dynamic chart and found help on a website, I have used
the
formula (displayed below) as a dynamic range formula for AllDates that
expands when more data is added to the column. The insructions stated that
in
the Define Names dialog box, type AllDates in the Name Box, and enter this
formula in the Refers To box:

=Data!$A$5:INDEX(Data!$A:$A,MATCH(9.99999E+307,Dat a!$A:$A))

The formula does work but I don't understand it, can anyone explain the
formula to me?
--
Many Thanks
Cath