Assign the cell address with a function

[Guest]

Hi all,
I want to calculate the sum of a column, for example the sum from A1 to A10
which is the function =sum(A1:A10). But instead of A10 which identifies the
last cell of the sum, I want the row number to be taken from a function that
returns an integer number. Thus, the last cell of the sum in the row may be
A10 or A12 etc according to the integer which my function will return. Can
anybody help me? If I should program it with macros how can I do that? Thanks
in advance.

 

[Ron Rosenfeld]

Hi all,
I want to calculate the sum of a column, for example the sum from A1 to A10
which is the function =sum(A1:A10). But instead of A10 which identifies the
last cell of the sum, I want the row number to be taken from a function that
returns an integer number. Thus, the last cell of the sum in the row may be
A10 or A12 etc according to the integer which my function will return. Can
anybody help me? If I should program it with macros how can I do that? Thanks
in advance.

You could use a formula of the type:

=SUM(OFFSET(A1,0,0,last_cell_row))

You'll have to adjust last_cell_row depending on the row of the first cell.

So if you don't start at A1, then the formula might have to be:

=SUM(OFFSET(A1,0,0,last_cell_row - ROW(first_cell_address)+1))






--ron

 

[Guest]

Thx ron U been very helpful!!!!

You could use a formula of the type:

=SUM(OFFSET(A1,0,0,last_cell_row))

You'll have to adjust last_cell_row depending on the row of the first cell.

So if you don't start at A1, then the formula might have to be:

=SUM(OFFSET(A1,0,0,last_cell_row - ROW(first_cell_address)+1))






--ron

 

[Guest]

=SUM(B2:INDIRECT("B"&ROWS(B1:B100)))

 

[Ron Rosenfeld]

Thx ron U been very helpful!!!!

You're welcome. Glad to help.
--ron

 

[T. Valko]

Try this:

D1 = a formula that returns an integer row number (per your description)

=SUM(A1:INDEX(A:A,D1))

If D1 is empty the formula will calculate the entire range.

Biff