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)
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.
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!