| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Assign the cell address with a function
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. |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Assign the cell address with a function
On Thu, 8 Feb 2007 17:16:00 -0800, T.Mad
wrote: 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 |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Assign the cell address with a function
Thx ron U been very helpful!!!!
"Ron Rosenfeld" wrote: On Thu, 8 Feb 2007 17:16:00 -0800, T.Mad wrote: 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 |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Assign the cell address with a function
=SUM(B2:INDIRECT("B"&ROWS(B1:B100)))
"T.Mad" wrote: 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. |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Assign the cell address with a function
On Thu, 8 Feb 2007 17:52:00 -0800, T.Mad
wrote: Thx ron U been very helpful!!!! "Ron Rosenfeld" wrote: On Thu, 8 Feb 2007 17:16:00 -0800, T.Mad wrote: 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 You're welcome. Glad to help. --ron |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Assign the cell address with a function
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 "T.Mad" wrote in message ... 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. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Get cell address from macro function argument | New Users to Excel | |||
| ADDRESS function - dynamic input cell | Excel Discussion (Misc queries) | |||
| Function syntax to compare cell contents | Excel Worksheet Functions | |||
| How do I obtain the address of a cell using the vlookup function? | Excel Worksheet Functions | |||
| How do I use a function to return the address of a cell? | Excel Worksheet Functions | |||
Linear Mode
