Summary of data in excel
 

I currently have a spreadsheet set up to import data from an external program
into a single excel sheet. I want to set up a summary page drawing data from
the single excel sheet. The data in the sheet refreshs from the external
source everytime you open it. So one time there might be 636 lines and the
next time there might be 637 lines. What is the best way to make the summary
page so the formula doesn't get messed up if a new line is inserted in the
data sheet.
--
Cheers
Lozza

Summary of data in excel
 

=SUM(INDIRECT("Sheet1!A1:A"&MAX(IF(Sheet1!A1:A500< "",ROW(1:500)))))


"Lozza65" skrev:

I currently have a spreadsheet set up to import data from an external program
into a single excel sheet. I want to set up a summary page drawing data from
the single excel sheet. The data in the sheet refreshs from the external
source everytime you open it. So one time there might be 636 lines and the
next time there might be 637 lines. What is the best way to make the summary
page so the formula doesn't get messed up if a new line is inserted in the
data sheet.
--
Cheers
Lozza

Summary of data in excel
 

Thanks for the information. I'm a little new to this type of formula. Where
in the spreadsheet do I put this formula?
Regards
Lorraine

"excelent" wrote:

=SUM(INDIRECT("Sheet1!A1:A"&MAX(IF(Sheet1!A1:A500< "",ROW(1:500)))))


"Lozza65" skrev:

I currently have a spreadsheet set up to import data from an external program
into a single excel sheet. I want to set up a summary page drawing data from
the single excel sheet. The data in the sheet refreshs from the external
source everytime you open it. So one time there might be 636 lines and the
next time there might be 637 lines. What is the best way to make the summary
page so the formula doesn't get messed up if a new line is inserted in the
data sheet.
--
Cheers
Lozza

Summary of data in excel
 

in the cell u want the result
an i forgot to say its an array-formel so insert hit F2 and
hit CTRL+SHIFT+ENTER

"Lozza65" skrev:

Thanks for the information. I'm a little new to this type of formula. Where
in the spreadsheet do I put this formula?
Regards
Lorraine

"excelent" wrote:

=SUM(INDIRECT("Sheet1!A1:A"&MAX(IF(Sheet1!A1:A500< "",ROW(1:500)))))


"Lozza65" skrev:

I currently have a spreadsheet set up to import data from an external program
into a single excel sheet. I want to set up a summary page drawing data from
the single excel sheet. The data in the sheet refreshs from the external
source everytime you open it. So one time there might be 636 lines and the
next time there might be 637 lines. What is the best way to make the summary
page so the formula doesn't get messed up if a new line is inserted in the
data sheet.
--
Cheers
Lozza