hello all:I don't follow exactly what you are looking for, but maybe you should use subtotal(9,x,y). When you finally do a sum of a column or row, the subtotals are ignored,
how to get SUM for a cell with more than 1 value, were each value is summed in that same cell.
coding:
TRY
oexcel=createobject('excel.application')
CATCH
mymessage(23,39,'excel application not installed/activated in this system',xm,xy,fcn,6,6,900)
dla6(24,39,'this report may crash',xm,xy,fcn,6,6,900);inkey(x)
END
oexcel=createobject('excel.application')
oexcel:workbooks:add()
osheet=oexcel:activesheet
...
...
do whil !eof()
...
for xxxx=1 to if(month=1.or.month=1.or.month=3.or.month=5.or.month=7.or.month=8.or.month=10.or.month=12,31,30)
if y1$'Ii';hrsandcharges=str(thisdayhrs[xxxx],2)+CHR(10)+str(thisdaycost[xxxx],8);end
cmemo+=chr(ktab)+if(y1$'Ii',["]+alltrim(hrsandcharges)+["],str(thisdayhrs[xxxx],2))
next
skip
endd
...
...
*after processing
GTSetClipboard(cMemo)
xsum(fromcell, tocell)
*in the same cell, should list different sums for both the thisdayhrs & thisdaycost of the same cell.
*********
func xsum(xs1,xs2)
*********
for xx=xs1 to xs2 oSheet:Cells(xrow,xx):Value="=SUM("+oSheet:Range(oSheet:Cells(nstart,xx),oSheet:Cells(xrow-1,xx)):Address( .F.,.F.)+")"
next
*thank you
On Wednesday, 10 November 2021 at 4:49:05 pm UTC+11, timec...@gmail.com wrote:
hello all:
how to get SUM for a cell with more than 1 value, were each value is summed in that same cell.
coding:
TRY
oexcel=createobject('excel.application')
CATCH
mymessage(23,39,'excel application not installed/activated in this system',xm,xy,fcn,6,6,900)
dla6(24,39,'this report may crash',xm,xy,fcn,6,6,900);inkey(x)
END
oexcel=createobject('excel.application')
oexcel:workbooks:add()
osheet=oexcel:activesheet
...
...
do whil !eof()
...
for xxxx=1 to if(month=1.or.month=1.or.month=3.or.month=5.or.month=7.or.month=8.or.month=10.or.month=12,31,30)
if y1$'Ii';hrsandcharges=str(thisdayhrs[xxxx],2)+CHR(10)+str(thisdaycost[xxxx],8);end
cmemo+=chr(ktab)+if(y1$'Ii',["]+alltrim(hrsandcharges)+["],str(thisdayhrs[xxxx],2))
next
skip
endd
...
...
*after processing
GTSetClipboard(cMemo)
xsum(fromcell, tocell)
*in the same cell, should list different sums for both the thisdayhrs & thisdaycost of the same cell.
*********
func xsum(xs1,xs2)
*********
for xx=xs1 to xs2 oSheet:Cells(xrow,xx):Value="=SUM("+oSheet:Range(oSheet:Cells(nstart,xx),oSheet:Cells(xrow-1,xx)):Address( .F.,.F.)+")"
next
*thank youI don't follow exactly what you are looking for, but maybe you should use subtotal(9,x,y). When you finally do a sum of a column or row, the subtotals are ignored,
Can you give an example of what your spreadsheet looks like, also I don't understand the :Address( .F.,.F.) part of the formula, I would suggest you drop it off and it should work OK.
oSheet:Cells(xrow,xx):Value="=SUM("+oSheet:Range(oSheet:Cells(nstart,xx),oSheet:Cells(xrow-1,xx)))
On Wednesday, 10 November 2021 at 4:49:05 pm UTC+11, timec...@gmail.com wrote:
hello all:
how to get SUM for a cell with more than 1 value, were each value is summed in that same cell.
coding:
TRY
oexcel=createobject('excel.application')
CATCH
mymessage(23,39,'excel application not installed/activated in this system',xm,xy,fcn,6,6,900)
dla6(24,39,'this report may crash',xm,xy,fcn,6,6,900);inkey(x)
END
oexcel=createobject('excel.application')
oexcel:workbooks:add()
osheet=oexcel:activesheet
...
...
do whil !eof()
...
for xxxx=1 to if(month=1.or.month=1.or.month=3.or.month=5.or.month=7.or.month=8.or.month=10.or.month=12,31,30)
if y1$'Ii';hrsandcharges=str(thisdayhrs[xxxx],2)+CHR(10)+str(thisdaycost[xxxx],8);end
cmemo+=chr(ktab)+if(y1$'Ii',["]+alltrim(hrsandcharges)+["],str(thisdayhrs[xxxx],2))
next
skip
endd
...
...
*after processing
GTSetClipboard(cMemo)
xsum(fromcell, tocell)
*in the same cell, should list different sums for both the thisdayhrs & thisdaycost of the same cell.
*********
func xsum(xs1,xs2)
*********
for xx=xs1 to xs2 oSheet:Cells(xrow,xx):Value="=SUM("+oSheet:Range(oSheet:Cells(nstart,xx),oSheet:Cells(xrow-1,xx)):Address( .F.,.F.)+")"
next
*thank youI don't follow exactly what you are looking for, but maybe you should use subtotal(9,x,y). When you finally do a sum of a column or row, the subtotals are ignored,
Can you give an example of what your spreadsheet looks like, also I don't understand the :Address( .F.,.F.) part of the formula, I would suggest you drop it off and it should work OK.
oSheet:Cells(xrow,xx):Value="=SUM("+oSheet:Range(oSheet:Cells(nstart,xx),oSheet:Cells(xrow-1,xx)))
On Monday, November 22, 2021 at 4:46:51 PM UTC+5:30, poopall wrote:
On Wednesday, 10 November 2021 at 4:49:05 pm UTC+11, timec...@gmail.com wrote:
hello all:
how to get SUM for a cell with more than 1 value, were each value is summed in that same cell.
coding:
TRY
oexcel=createobject('excel.application')
CATCH
mymessage(23,39,'excel application not installed/activated in this system',xm,xy,fcn,6,6,900)
dla6(24,39,'this report may crash',xm,xy,fcn,6,6,900);inkey(x)
END
oexcel=createobject('excel.application')
oexcel:workbooks:add()
osheet=oexcel:activesheet
...
...
do whil !eof()
...
for xxxx=1 to if(month=1.or.month=1.or.month=3.or.month=5.or.month=7.or.month=8.or.month=10.or.month=12,31,30)
if y1$'Ii';hrsandcharges=str(thisdayhrs[xxxx],2)+CHR(10)+str(thisdaycost[xxxx],8);end
cmemo+=chr(ktab)+if(y1$'Ii',["]+alltrim(hrsandcharges)+["],str(thisdayhrs[xxxx],2))
next
skip
endd
...
...
*after processing
GTSetClipboard(cMemo)
xsum(fromcell, tocell)
*in the same cell, should list different sums for both the thisdayhrs & thisdaycost of the same cell.
*********
func xsum(xs1,xs2)
*********
for xx=xs1 to xs2 oSheet:Cells(xrow,xx):Value="=SUM("+oSheet:Range(oSheet:Cells(nstart,xx),oSheet:Cells(xrow-1,xx)):Address( .F.,.F.)+")"
next
*thank youI don't follow exactly what you are looking for, but maybe you should use subtotal(9,x,y). When you finally do a sum of a column or row, the subtotals are ignored,
Can you give an example of what your spreadsheet looks like, also I don't understand the :Address( .F.,.F.) part of the formula, I would suggest you drop it off and it should work OK.
oSheet:Cells(xrow,xx):Value="=SUM("+oSheet:Range(oSheet:Cells(nstart,xx),oSheet:Cells(xrow-1,xx)))thanks poopall:
let me rephrase my query:
in a 'single' cell, i have separate figures for 'cost & discount & tax'
e.g.: from cell D6 to D134 value=str(cost)+chr(10)+str(disc)+chr(10)+str(tax) [single cell will list 3 separate figures in 3 separate rows]
at end of my loop/process, with what formula/sum, can i get individual totals/sum (D6-D134) of 'cost, disc, tax' in the same cell (3 separate rows, but in same/single cell)
also, if the user edits discount in cell D110 , only the sum of discount should auto change.
thanks
p.s.
'also I don't understand the :Address( .F.,.F.) part of the formula, I would suggest you drop it off and it should work OK.'
sure, will try without address(....
On Tuesday, 30 November 2021 at 5:56:19 pm UTC+11, timec...@gmail.com wrote:hi
On Monday, November 22, 2021 at 4:46:51 PM UTC+5:30, poopall wrote:
On Wednesday, 10 November 2021 at 4:49:05 pm UTC+11, timec...@gmail.com wrote:
hello all:
how to get SUM for a cell with more than 1 value, were each value is summed in that same cell.
coding:
TRY
oexcel=createobject('excel.application')
CATCH
mymessage(23,39,'excel application not installed/activated in this system',xm,xy,fcn,6,6,900)
dla6(24,39,'this report may crash',xm,xy,fcn,6,6,900);inkey(x)
END
oexcel=createobject('excel.application')
oexcel:workbooks:add()
osheet=oexcel:activesheet
...
...
do whil !eof()
...
for xxxx=1 to if(month=1.or.month=1.or.month=3.or.month=5.or.month=7.or.month=8.or.month=10.or.month=12,31,30)
if y1$'Ii';hrsandcharges=str(thisdayhrs[xxxx],2)+CHR(10)+str(thisdaycost[xxxx],8);end
cmemo+=chr(ktab)+if(y1$'Ii',["]+alltrim(hrsandcharges)+["],str(thisdayhrs[xxxx],2))
next
skip
endd
...
...
*after processing
GTSetClipboard(cMemo)
xsum(fromcell, tocell)
*in the same cell, should list different sums for both the thisdayhrs & thisdaycost of the same cell.
*********
func xsum(xs1,xs2)
*********
for xx=xs1 to xs2 oSheet:Cells(xrow,xx):Value="=SUM("+oSheet:Range(oSheet:Cells(nstart,xx),oSheet:Cells(xrow-1,xx)):Address( .F.,.F.)+")"
next
*thank youI don't follow exactly what you are looking for, but maybe you should use subtotal(9,x,y). When you finally do a sum of a column or row, the subtotals are ignored,
Can you give an example of what your spreadsheet looks like, also I don't understand the :Address( .F.,.F.) part of the formula, I would suggest you drop it off and it should work OK.
oSheet:Cells(xrow,xx):Value="=SUM("+oSheet:Range(oSheet:Cells(nstart,xx),oSheet:Cells(xrow-1,xx)))thanks poopall:
let me rephrase my query:
in a 'single' cell, i have separate figures for 'cost & discount & tax' e.g.: from cell D6 to D134 value=str(cost)+chr(10)+str(disc)+chr(10)+str(tax) [single cell will list 3 separate figures in 3 separate rows]
at end of my loop/process, with what formula/sum, can i get individual totals/sum (D6-D134) of 'cost, disc, tax' in the same cell (3 separate rows, but in same/single cell)
also, if the user edits discount in cell D110 , only the sum of discount should auto change.
thanks
p.s.
'also I don't understand the :Address( .F.,.F.) part of the formula, I would suggest you drop it off and it should work OK.'
sure, will try without address(....Can you send an example of what you want the spreadsheet to look like
On Wednesday, December 1, 2021 at 4:14:17 PM UTC+5:30, poopall wrote:Whats the delimiter of the individual cells have you used a comma, as you state you have 3 seperate amounts in there I assume stored in the cell as a string ?
On Tuesday, 30 November 2021 at 5:56:19 pm UTC+11, timec...@gmail.com wrote:
On Monday, November 22, 2021 at 4:46:51 PM UTC+5:30, poopall wrote:
On Wednesday, 10 November 2021 at 4:49:05 pm UTC+11, timec...@gmail.com wrote:
hello all:
how to get SUM for a cell with more than 1 value, were each value is summed in that same cell.
coding:
TRY
oexcel=createobject('excel.application')
CATCH
mymessage(23,39,'excel application not installed/activated in this system',xm,xy,fcn,6,6,900)
dla6(24,39,'this report may crash',xm,xy,fcn,6,6,900);inkey(x)
END
oexcel=createobject('excel.application')
oexcel:workbooks:add()
osheet=oexcel:activesheet
...
...
do whil !eof()
...
for xxxx=1 to if(month=1.or.month=1.or.month=3.or.month=5.or.month=7.or.month=8.or.month=10.or.month=12,31,30)
if y1$'Ii';hrsandcharges=str(thisdayhrs[xxxx],2)+CHR(10)+str(thisdaycost[xxxx],8);end
cmemo+=chr(ktab)+if(y1$'Ii',["]+alltrim(hrsandcharges)+["],str(thisdayhrs[xxxx],2))
next
skip
endd
...
...
*after processing
GTSetClipboard(cMemo)
xsum(fromcell, tocell)
*in the same cell, should list different sums for both the thisdayhrs & thisdaycost of the same cell.
*********
func xsum(xs1,xs2)
*********
for xx=xs1 to xs2 oSheet:Cells(xrow,xx):Value="=SUM("+oSheet:Range(oSheet:Cells(nstart,xx),oSheet:Cells(xrow-1,xx)):Address( .F.,.F.)+")"
next
*thank youI don't follow exactly what you are looking for, but maybe you should use subtotal(9,x,y). When you finally do a sum of a column or row, the subtotals are ignored,
Can you give an example of what your spreadsheet looks like, also I don't understand the :Address( .F.,.F.) part of the formula, I would suggest you drop it off and it should work OK.
oSheet:Cells(xrow,xx):Value="=SUM("+oSheet:Range(oSheet:Cells(nstart,xx),oSheet:Cells(xrow-1,xx)))thanks poopall:
let me rephrase my query:
in a 'single' cell, i have separate figures for 'cost & discount & tax' e.g.: from cell D6 to D134 value=str(cost)+chr(10)+str(disc)+chr(10)+str(tax) [single cell will list 3 separate figures in 3 separate rows]
at end of my loop/process, with what formula/sum, can i get individual totals/sum (D6-D134) of 'cost, disc, tax' in the same cell (3 separate rows, but in same/single cell)
also, if the user edits discount in cell D110 , only the sum of discount should auto change.
thanks
p.s.
'also I don't understand the :Address( .F.,.F.) part of the formula, I would suggest you drop it off and it should work OK.'
hisure, will try without address(....Can you send an example of what you want the spreadsheet to look like
totals of 'cost, disc, tax' in the same/single cell
i.e. cell D135 should show *separate/individual* sums of cost,disc,tax (D6-D134) as 3 separate rows. (cost,disc,tax are 3 diff. variables)
*e.g.: in D135
1334345 (1334345 being sum of cost D6-D134)
554567 (554... being sum of disc D6-D134)
67418884 (6741... being disc of tax D6-D134)
*
thanks
p.s. oSheet:Cells(xrow,xx):Value="=SUM("+oSheet:Range(oSheet:Cells(nstart,xx),oSheet:Cells(xrow-1,xx))+")"
sums only the 'cost'.
On Thursday, 2 December 2021 at 3:45:46 pm UTC+11, timec...@gmail.com wrote:
On Wednesday, December 1, 2021 at 4:14:17 PM UTC+5:30, poopall wrote:
On Tuesday, 30 November 2021 at 5:56:19 pm UTC+11, timec...@gmail.com wrote:
On Monday, November 22, 2021 at 4:46:51 PM UTC+5:30, poopall wrote:
On Wednesday, 10 November 2021 at 4:49:05 pm UTC+11, timec...@gmail.com wrote:
hello all:
how to get SUM for a cell with more than 1 value, were each value is summed in that same cell.
coding:
TRY
oexcel=createobject('excel.application')
CATCH
mymessage(23,39,'excel application not installed/activated in this system',xm,xy,fcn,6,6,900)
dla6(24,39,'this report may crash',xm,xy,fcn,6,6,900);inkey(x)
END
oexcel=createobject('excel.application')
oexcel:workbooks:add()
osheet=oexcel:activesheet
...
...
do whil !eof()
...
for xxxx=1 to if(month=1.or.month=1.or.month=3.or.month=5.or.month=7.or.month=8.or.month=10.or.month=12,31,30)
if y1$'Ii';hrsandcharges=str(thisdayhrs[xxxx],2)+CHR(10)+str(thisdaycost[xxxx],8);end
cmemo+=chr(ktab)+if(y1$'Ii',["]+alltrim(hrsandcharges)+["],str(thisdayhrs[xxxx],2))
next
skip
endd
...
...
*after processing
GTSetClipboard(cMemo)
xsum(fromcell, tocell)
*in the same cell, should list different sums for both the thisdayhrs & thisdaycost of the same cell.
*********
func xsum(xs1,xs2)
*********
for xx=xs1 to xs2 oSheet:Cells(xrow,xx):Value="=SUM("+oSheet:Range(oSheet:Cells(nstart,xx),oSheet:Cells(xrow-1,xx)):Address( .F.,.F.)+")"
next
*thank youI don't follow exactly what you are looking for, but maybe you should use subtotal(9,x,y). When you finally do a sum of a column or row, the subtotals are ignored,
Can you give an example of what your spreadsheet looks like, also I don't understand the :Address( .F.,.F.) part of the formula, I would suggest you drop it off and it should work OK.
oSheet:Cells(xrow,xx):Value="=SUM("+oSheet:Range(oSheet:Cells(nstart,xx),oSheet:Cells(xrow-1,xx)))thanks poopall:
let me rephrase my query:
in a 'single' cell, i have separate figures for 'cost & discount & tax' e.g.: from cell D6 to D134 value=str(cost)+chr(10)+str(disc)+chr(10)+str(tax) [single cell will list 3 separate figures in 3 separate rows]
at end of my loop/process, with what formula/sum, can i get individual totals/sum (D6-D134) of 'cost, disc, tax' in the same cell (3 separate rows, but in same/single cell)
also, if the user edits discount in cell D110 , only the sum of discount should auto change.
thanks
p.s.
'also I don't understand the :Address( .F.,.F.) part of the formula, I would suggest you drop it off and it should work OK.'
hisure, will try without address(....Can you send an example of what you want the spreadsheet to look like
totals of 'cost, disc, tax' in the same/single cell
i.e. cell D135 should show *separate/individual* sums of cost,disc,tax (D6-D134) as 3 separate rows. (cost,disc,tax are 3 diff. variables)
*e.g.: in D135
1334345 (1334345 being sum of cost D6-D134)
554567 (554... being sum of disc D6-D134)
67418884 (6741... being disc of tax D6-D134)
*
thanks
p.s. oSheet:Cells(xrow,xx):Value="=SUM("+oSheet:Range(oSheet:Cells(nstart,xx),oSheet:Cells(xrow-1,xx))+")"Whats the delimiter of the individual cells have you used a comma, as you state you have 3 seperate amounts in there I assume stored in the cell as a string ?
sums only the 'cost'.
On Friday, December 3, 2021 at 10:04:34 AM UTC+5:30, poopall wrote:
On Thursday, 2 December 2021 at 3:45:46 pm UTC+11, timec...@gmail.com wrote:
On Wednesday, December 1, 2021 at 4:14:17 PM UTC+5:30, poopall wrote:
On Tuesday, 30 November 2021 at 5:56:19 pm UTC+11, timec...@gmail.com wrote:
On Monday, November 22, 2021 at 4:46:51 PM UTC+5:30, poopall wrote:
On Wednesday, 10 November 2021 at 4:49:05 pm UTC+11, timec...@gmail.com wrote:
hello all:
how to get SUM for a cell with more than 1 value, were each value is summed in that same cell.
coding:
TRY
oexcel=createobject('excel.application')
CATCH
mymessage(23,39,'excel application not installed/activated in this system',xm,xy,fcn,6,6,900)
dla6(24,39,'this report may crash',xm,xy,fcn,6,6,900);inkey(x) END
oexcel=createobject('excel.application')
oexcel:workbooks:add()
osheet=oexcel:activesheet
...
...
do whil !eof()
...
for xxxx=1 to if(month=1.or.month=1.or.month=3.or.month=5.or.month=7.or.month=8.or.month=10.or.month=12,31,30)
if y1$'Ii';hrsandcharges=str(thisdayhrs[xxxx],2)+CHR(10)+str(thisdaycost[xxxx],8);end
cmemo+=chr(ktab)+if(y1$'Ii',["]+alltrim(hrsandcharges)+["],str(thisdayhrs[xxxx],2))
next
skip
endd
...
...
*after processing
GTSetClipboard(cMemo)
xsum(fromcell, tocell)
*in the same cell, should list different sums for both the thisdayhrs & thisdaycost of the same cell.
*********
func xsum(xs1,xs2)
*********
for xx=xs1 to xs2 oSheet:Cells(xrow,xx):Value="=SUM("+oSheet:Range(oSheet:Cells(nstart,xx),oSheet:Cells(xrow-1,xx)):Address( .F.,.F.)+")"
next
*thank youI don't follow exactly what you are looking for, but maybe you should use subtotal(9,x,y). When you finally do a sum of a column or row, the subtotals are ignored,
Can you give an example of what your spreadsheet looks like, also I don't understand the :Address( .F.,.F.) part of the formula, I would suggest you drop it off and it should work OK.
oSheet:Cells(xrow,xx):Value="=SUM("+oSheet:Range(oSheet:Cells(nstart,xx),oSheet:Cells(xrow-1,xx)))thanks poopall:
let me rephrase my query:
in a 'single' cell, i have separate figures for 'cost & discount & tax'
e.g.: from cell D6 to D134 value=str(cost)+chr(10)+str(disc)+chr(10)+str(tax) [single cell will list 3 separate figures in 3 separate rows]
at end of my loop/process, with what formula/sum, can i get individual totals/sum (D6-D134) of 'cost, disc, tax' in the same cell (3 separate rows, but in same/single cell)
also, if the user edits discount in cell D110 , only the sum of discount should auto change.
thanks
p.s.
'also I don't understand the :Address( .F.,.F.) part of the formula, I would suggest you drop it off and it should work OK.'
hisure, will try without address(....Can you send an example of what you want the spreadsheet to look like
totals of 'cost, disc, tax' in the same/single cell
i.e. cell D135 should show *separate/individual* sums of cost,disc,tax (D6-D134) as 3 separate rows. (cost,disc,tax are 3 diff. variables)
*e.g.: in D135
1334345 (1334345 being sum of cost D6-D134)
554567 (554... being sum of disc D6-D134)
67418884 (6741... being disc of tax D6-D134)
*
thanks
str(cost,n)+chr(10)+str(disc,n)+chr(10)+str(tax,n)p.s. oSheet:Cells(xrow,xx):Value="=SUM("+oSheet:Range(oSheet:Cells(nstart,xx),oSheet:Cells(xrow-1,xx))+")"Whats the delimiter of the individual cells have you used a comma, as you state you have 3 seperate amounts in there I assume stored in the cell as a string ?
sums only the 'cost'.
so after process (creation of the .xlsx file), each cell shows:
12345
6789
1123666
in a single cell. (12345 being cost, 6789 being disc...)
On Friday, 3 December 2021 at 6:52:19 pm UTC+11, timec...@gmail.com wrote:break them up, then convert to numeric and then do something else
On Friday, December 3, 2021 at 10:04:34 AM UTC+5:30, poopall wrote:
On Thursday, 2 December 2021 at 3:45:46 pm UTC+11, timec...@gmail.com wrote:
On Wednesday, December 1, 2021 at 4:14:17 PM UTC+5:30, poopall wrote:
On Tuesday, 30 November 2021 at 5:56:19 pm UTC+11, timec...@gmail.com wrote:
On Monday, November 22, 2021 at 4:46:51 PM UTC+5:30, poopall wrote:
On Wednesday, 10 November 2021 at 4:49:05 pm UTC+11, timec...@gmail.com wrote:
hello all:
how to get SUM for a cell with more than 1 value, were each value is summed in that same cell.
coding:
TRY
oexcel=createobject('excel.application')
CATCH
mymessage(23,39,'excel application not installed/activated in this system',xm,xy,fcn,6,6,900)
dla6(24,39,'this report may crash',xm,xy,fcn,6,6,900);inkey(x) END
oexcel=createobject('excel.application') oexcel:workbooks:add()
osheet=oexcel:activesheet
...
...
do whil !eof()
...
for xxxx=1 to if(month=1.or.month=1.or.month=3.or.month=5.or.month=7.or.month=8.or.month=10.or.month=12,31,30)
if y1$'Ii';hrsandcharges=str(thisdayhrs[xxxx],2)+CHR(10)+str(thisdaycost[xxxx],8);end
cmemo+=chr(ktab)+if(y1$'Ii',["]+alltrim(hrsandcharges)+["],str(thisdayhrs[xxxx],2))
next
skip
endd
...
...
*after processing
GTSetClipboard(cMemo)
xsum(fromcell, tocell)
*in the same cell, should list different sums for both the thisdayhrs & thisdaycost of the same cell.
*********
func xsum(xs1,xs2)
*********
for xx=xs1 to xs2 oSheet:Cells(xrow,xx):Value="=SUM("+oSheet:Range(oSheet:Cells(nstart,xx),oSheet:Cells(xrow-1,xx)):Address( .F.,.F.)+")"
next
*thank youI don't follow exactly what you are looking for, but maybe you should use subtotal(9,x,y). When you finally do a sum of a column or row, the subtotals are ignored,
Can you give an example of what your spreadsheet looks like, also I don't understand the :Address( .F.,.F.) part of the formula, I would suggest you drop it off and it should work OK.
oSheet:Cells(xrow,xx):Value="=SUM("+oSheet:Range(oSheet:Cells(nstart,xx),oSheet:Cells(xrow-1,xx)))thanks poopall:
let me rephrase my query:
in a 'single' cell, i have separate figures for 'cost & discount & tax'
e.g.: from cell D6 to D134 value=str(cost)+chr(10)+str(disc)+chr(10)+str(tax) [single cell will list 3 separate figures in 3 separate rows]
at end of my loop/process, with what formula/sum, can i get individual totals/sum (D6-D134) of 'cost, disc, tax' in the same cell (3 separate rows, but in same/single cell)
also, if the user edits discount in cell D110 , only the sum of discount should auto change.
thanks
p.s.
'also I don't understand the :Address( .F.,.F.) part of the formula, I would suggest you drop it off and it should work OK.'
hisure, will try without address(....Can you send an example of what you want the spreadsheet to look like
totals of 'cost, disc, tax' in the same/single cell
i.e. cell D135 should show *separate/individual* sums of cost,disc,tax (D6-D134) as 3 separate rows. (cost,disc,tax are 3 diff. variables)
*e.g.: in D135
1334345 (1334345 being sum of cost D6-D134)
554567 (554... being sum of disc D6-D134)
67418884 (6741... being disc of tax D6-D134)
*
thanks
str(cost,n)+chr(10)+str(disc,n)+chr(10)+str(tax,n)p.s. oSheet:Cells(xrow,xx):Value="=SUM("+oSheet:Range(oSheet:Cells(nstart,xx),oSheet:Cells(xrow-1,xx))+")"Whats the delimiter of the individual cells have you used a comma, as you state you have 3 seperate amounts in there I assume stored in the cell as a string ?
sums only the 'cost'.
so after process (creation of the .xlsx file), each cell shows:I think the process would be difficult in excel, as you would first have a write a formulae to break up this string into each element, using something like instr() function in excel to locate the delimiter chr(10), then use left , mid and right to
12345
6789
1123666
in a single cell. (12345 being cost, 6789 being disc...)
Is there a reason why each value of cost, disc, tax cannot be written to separate cells, even if these are to another sheet in the same workbook, and then everything else would be relatively easy. The spreadsheet could always be hidden or locked if youdid not users to see the content.
Sysop: | Keyop |
---|---|
Location: | Huddersfield, West Yorkshire, UK |
Users: | 171 |
Nodes: | 16 (1 / 15) |
Uptime: | 13:02:42 |
Calls: | 3,402 |
Calls today: | 10 |
Files: | 10,830 |
Messages: | 3,208,964 |
Posted today: | 1 |