Auto Select New Data

I have a very large spreadsheet that changes daily, new rows are added and some rows are deleted. I have many columns with formulas that I have to manually update the formulas to add the new rows of data.

Basically,in B1, I have a formula =sum(B2:B5)
If I add a formula or data to B6, I have to modify the formula in B1 to include the new field of data.

Can I create a formula that will look at all cells from B3 to the end of the column, find the last cell that has data or a formula in it, and calculate the sum from B3 to that last cell containing a formula?

I have tried Index and Match, but cannot get a formula to work.

Can someone please help.


Frank

Frank,

I'd recommend setting up a named range and then using that named range in your formula.

Assuming there are no blank cells in column B, the named range formula could be:
=INDIRECT("'Sheet1'!$B$3:$B$"&COUNTA('Sheet1'!$B:$B))

And you could name it whatever you want. In this example. we'll call it BCells. Then your formula in B1 would become:
=SUM(BCells)

Hope that helps,
~tigeravatar

tigeravatar,

Appreciate the help, that worked just fine; have had no experience with the 'Indirect' function before.

Thanks again.

Frank