Excel sum formula for column data that varies

Scripting Forums AppleScript | Mac OS X
#1

All,

I need a simple script that places the native Excel sum formula in a cell with range references based on the number of rows of data in that column. (I cannot use a pivot table for this because I need to display the native formula).

As an example; if data in column “A” runs from the second row to row five, the formula in the sixth row should be “=sum(a2:a5)”. If data in column “A” ends up running from the second row to row ten, the formula in the eleventh row should be “=sum(a2:a10)”.

I have the script that identifies where the formula should be placed. The problem I’m having is in creating the applescript that identifies the number of used rows in the column and creates the native formula. I have tried variations of the r1c1 format without success.

Thank you in advance!

Model: iMac
Browser: Safari 534.57.2
Operating System: Mac OS X (10.7)

#2

Could you use the formula =SUM(A:A) instead of =SUM(A2:A10)
(If the formula is going in A1 you could use a formula like =SUM(A2:A65536))

Alternatly, you could use a non-scripting approach and use Excel’s Name feature.

Define a name
Name: myRange
RefersTo: =INDEX(Sheet1!$A:$A, 2, 1):INDEX(Sheet1!$A:$A, MATCH(9.99E+307, Sheet1!$A:$A), 1)

and then put =SUM(myRange) in a cell

For the scripting solution (from memory, there might be some synatax errors0

Set LastCell to Get End Range "A65536" direction towards the top
Set myFormula to "=SUM(A2:A" & first Row Index of LastCell & ")"
#3

Thank you mikerickson!

Your scripting solution from memory did the trick. (I just had to remove the s from towards).

set LastCell to get end range “A65536” direction toward the top
set myFormula to “=SUM(A2:A” & first row index of LastCell & “)”

#4

Did you look at the Name solution.
The advantage of that approach is that the formula will adjust as more rows of data are added, without haveing to re-run the script.

One could bypass the Name altogether. Run this script once and the worksheet formula will adjust itself as new data is entered.

Set Formula of someCell to "=SUM(INDEX(Sheet1!$A:$A, 2, 1):INDEX(Sheet1!$A:$A, MATCH(9.99E+307, Sheet1!$A:$A), 1))"

The adjusting formula that you seek can be done entirely in Excel.

#5

I can use the name solution in other sheets. For this application the sheet is used as part of a final printed report (which is only produced from a selection in the main script). Thank you for both!