Select First Cell In Range Relative To Last Cell In A Different Range

[pnguyen05]

Hi,

Below are the requirements:

1. How do I select A4:A5 dynamically so that it'll take into consideration whenever a user deletes or adds a row? For example, if a user adds a row within that range, the code needs to recognize that and select appropriate range of cells.
2. Put the sum of the subtotals into the Total (A6) cell. This also needs to be dynamic so if a user add a row, the cell would now be A7.

Thank you

	A
1	10
2	20
3	Total 30
4	20
5	30
6	Total 50

 

[pnguyen05]

Hi,

Below are the requirements:

1. How do I select A4:A5 dynamically so that it'll take into consideration whenever a user deletes or adds a row? For example, if a user adds a row within that range, the code needs to recognize that and select appropriate range of cells.
2. Put the sum of the subtotals into the Total (A6) cell. This also needs to be dynamic so if a user add a row, the cell would now be A7.

Thank you

	A
1	10
2	20
3	Total 30
4	20
5	30
6	Total 50

Or should I try using a loop?

 

[Scott Huish]

What determines when you put a total?

 

[pnguyen05]

What determines when you put a total?

I don't really understand this question. Could you please expand? The Total would be the sum of the values within that range. So Total 50 (A6) is sum of the values in the A4:A5 range.

I realized I said subtotals in my original post, I apologize. It should be values.

 

[Scott Huish]

You have two totals in your example. One in A3 and one in A6.
If there were no values in the spreadsheet at all and you started adding them, at what point would you total them?

Why doesn't the total include the A1 and A2 range as well. Like I asked what determines where the totals go. Every two cells or what?

 

[pnguyen05]

You have two totals in your example. One in A3 and one in A6.
If there were no values in the spreadsheet at all and you started adding them, at what point would you total them?

Why doesn't the total include the A1 and A2 range as well. Like I asked what determines where the totals go. Every two cells or what?

It doesn't include A1 and A2 range as well because each total represents a different category. For example, instead of Total 1, it's Total Debit and instead of Total 2, it's Total Credit. Therefore, whatever values included from the beginning to the very last cell in the range before Total 1 would be the sum for Total 1. Then the range would "reset" for Total 2, so whatever values after Total 1 to the very last cell in the range before Total 2 would be the sum for Total 2.

 

[duggie33]

There is likely a better way to do this, but this is one way to deal with summing values in a range and then inserting rows into that range. This should handle inserting a row to be the first or last in the range to be summed. Create a named range for the first and last cells in the range using OFFSET.

Book1
	A
1	Header
2	50
3	10
4	100
5	20
6	200
7	380
Sheet4

Cell Formulas
Range	Formula
A7	A7	=SUM((start_1):(end_1))

Named Ranges
Name	Refers To	Cells
end_1	=OFFSET(Sheet4!$A$7,-1,0)	A7
start_1	=OFFSET(Sheet4!$A$1,1,0)	A7

Hope that helps,

Doug