How does this sum sheet1 A1 to the last thing in column A without putting a
number or foumula in D1? =SUM(Sheet1!A1:INDEX(Sheet1!A:A,$D$1))



I have use this in D1, =MATCH(9.99999999999999E+307,Sheet1!A:A)

, before to use this kind of formula but today I put the formula in without
anything in D1 and it looks like it works????



Is this the best way to do this??



Thanks, using excel 2003

Start he

From XL Help ("INDEX"):

If you set row_num or column_num to 0 (zero),
INDEX returns the reference for the entire column
or row, respectively


In article ,
"Paul B" wrote:

How does this sum sheet1 A1 to the last thing in column A without putting a
number or foumula in D1? =SUM(Sheet1!A1:INDEX(Sheet1!A:A,$D$1))



I have use this in D1, =MATCH(9.99999999999999E+307,Sheet1!A:A)

, before to use this kind of formula but today I put the formula in without
anything in D1 and it looks like it works????



Is this the best way to do this??



Thanks, using excel 2003

Hi,

If you don't specify the second argument is assumes all, so you could
actually replace

=SUM(Sheet1!A1:INDEX(Sheet1!A:A,$D$1))

with

=SUM(Sheet1!A1:INDEX(Sheet1!A:A,))

And it would still work. However, this defeats the purpose of the formula,
which is to control how many cells are summed. If you just want to sum them
all, this formula is overkill, you should just use =SUM(A:A)

--
If this helps, please click the Yes button

Cheers,
Shane Devenshire


"Paul B" wrote:

How does this sum sheet1 A1 to the last thing in column A without putting a
number or foumula in D1? =SUM(Sheet1!A1:INDEX(Sheet1!A:A,$D$1))



I have use this in D1, =MATCH(9.99999999999999E+307,Sheet1!A:A)

, before to use this kind of formula but today I put the formula in without
anything in D1 and it looks like it works????



Is this the best way to do this??



Thanks, using excel 2003

I was trying to incorporate it in this formula to get the last row, is there
another way to do it with a formula like this?



SUMPRODUCT((Data!$A$3:$A$579=$B52)*(YEAR(Data!$B$3 :$B$579)=$A52),Data!F$3:F$579)



Column A is names

Column B is dates

Column F is price



Thanks



"JE McGimpsey" wrote in message
...
Start he

From XL Help ("INDEX"):

If you set row_num or column_num to 0 (zero),
INDEX returns the reference for the entire column
or row, respectively


In article ,
"Paul B" wrote:

How does this sum sheet1 A1 to the last thing in column A without putting
a
number or foumula in D1? =SUM(Sheet1!A1:INDEX(Sheet1!A:A,$D$1))



I have use this in D1, =MATCH(9.99999999999999E+307,Sheet1!A:A)

, before to use this kind of formula but today I put the formula in
without
anything in D1 and it looks like it works????



Is this the best way to do this??



Thanks, using excel 2003