Rocksolid Light

Welcome to RetroBBS

mail  files  register  newsreader  groups  login

Message-ID:  

"If it ain't broke, don't fix it." -- Bert Lantz

devel / comp.lang.xharbour / Re: osheet:cells(X,Y):=value=IF(a=b,c,if(...))

SubjectAuthor
* osheet:cells(X,Y):=value=IF(a=b,c,if(...))timepro timesheet
+- Re: osheet:cells(X,Y):=value=IF(a=b,c,if(...))poopall
`* Re: osheet:cells(X,Y):=value=IF(a=b,c,if(...))poopall
 `* Re: osheet:cells(X,Y):=value=IF(a=b,c,if(...))timepro timesheet
  +* Re: osheet:cells(X,Y):=value=IF(a=b,c,if(...))timepro timesheet
  |+- Re: osheet:cells(X,Y):=value=IF(a=b,c,if(...))timepro timesheet
  |`* Re: osheet:cells(X,Y):=value=IF(a=b,c,if(...))poopall
  | `* Re: osheet:cells(X,Y):=value=IF(a=b,c,if(...))PARESH VALIA
  |  `* Re: osheet:cells(X,Y):=value=IF(a=b,c,if(...))poopall
  |   +- Re: osheet:cells(X,Y):=value=IF(a=b,c,if(...))PARESH VALIA
  |   +- Re: osheet:cells(X,Y):=value=IF(a=b,c,if(...))timepro timesheet
  |   `- Re: osheet:cells(X,Y):=value=IF(a=b,c,if(...))timepro timesheet
  `- Re: osheet:cells(X,Y):=value=IF(a=b,c,if(...))poopall

1
osheet:cells(X,Y):=value=IF(a=b,c,if(...))

<bc08c27f-cd5a-47e5-ac5c-1e014c590444n@googlegroups.com>

  copy mid

https://www.rocksolidbbs.com/devel/article-flat.php?id=16915&group=comp.lang.xharbour#16915

  copy link   Newsgroups: comp.lang.xharbour
X-Received: by 2002:a05:620a:9c3:: with SMTP id y3mr2195991qky.367.1641879131683;
Mon, 10 Jan 2022 21:32:11 -0800 (PST)
X-Received: by 2002:ac8:4e4e:: with SMTP id e14mr2500198qtw.108.1641879131555;
Mon, 10 Jan 2022 21:32:11 -0800 (PST)
Path: i2pn2.org!i2pn.org!weretis.net!feeder6.news.weretis.net!news.misty.com!border2.nntp.dca1.giganews.com!nntp.giganews.com!news-out.google.com!nntp.google.com!postnews.google.com!google-groups.googlegroups.com!not-for-mail
Newsgroups: comp.lang.xharbour
Date: Mon, 10 Jan 2022 21:32:11 -0800 (PST)
Injection-Info: google-groups.googlegroups.com; posting-host=2401:4900:1c20:6ad5:6431:9dcc:2b49:da87;
posting-account=jZAeNwoAAABYWjMRM-FfqKkHfC3Iph0l
NNTP-Posting-Host: 2401:4900:1c20:6ad5:6431:9dcc:2b49:da87
User-Agent: G2/1.0
MIME-Version: 1.0
Message-ID: <bc08c27f-cd5a-47e5-ac5c-1e014c590444n@googlegroups.com>
Subject: osheet:cells(X,Y):=value=IF(a=b,c,if(...))
From: timecosting@gmail.com (timepro timesheet)
Injection-Date: Tue, 11 Jan 2022 05:32:11 +0000
Content-Type: text/plain; charset="UTF-8"
Lines: 20
 by: timepro timesheet - Tue, 11 Jan 2022 05:32 UTC

how do i program/syntax:

1.
for xloop=xrowstart to xrowend

osheet:cells(xloop,16):value=opbal && P
osheet:cells(xloop,17):value=debits && Q
osheet:cells(xloop,18):value=credits && R
osheet:cells(xloop,19):value="This Is The TaxCredit" && S
osheet:cells(xloop,20):value="="+"P"+alltrim(str(xloop))+"-"+"Q"+alltrim(str(xloop))+"+"+"R"+alltrim(str(xloop)) && T
*this is where i am stuck
osheet:cells(xloop,21):value=if("T"+alltrim(str(xloop))>'0','cr',if("T"+alltrim(str(loop))='0',originalbalance,'dr')) && originalbalance is a predefined variable

next

2. how to assign the value of a cell to a variable
e.g.
thetax=osheet:cells(xxx,yyy)
@ rr,cc say thetax

thanks

Re: osheet:cells(X,Y):=value=IF(a=b,c,if(...))

<ff72eb5b-500b-443a-a5f3-531c9eff3c9en@googlegroups.com>

  copy mid

https://www.rocksolidbbs.com/devel/article-flat.php?id=16916&group=comp.lang.xharbour#16916

  copy link   Newsgroups: comp.lang.xharbour
X-Received: by 2002:ac8:7f8b:: with SMTP id z11mr2655666qtj.396.1641883607896;
Mon, 10 Jan 2022 22:46:47 -0800 (PST)
X-Received: by 2002:a37:9c53:: with SMTP id f80mr2285676qke.218.1641883607573;
Mon, 10 Jan 2022 22:46:47 -0800 (PST)
Path: i2pn2.org!i2pn.org!weretis.net!feeder6.news.weretis.net!news.misty.com!border2.nntp.dca1.giganews.com!nntp.giganews.com!news-out.google.com!nntp.google.com!postnews.google.com!google-groups.googlegroups.com!not-for-mail
Newsgroups: comp.lang.xharbour
Date: Mon, 10 Jan 2022 22:46:47 -0800 (PST)
In-Reply-To: <bc08c27f-cd5a-47e5-ac5c-1e014c590444n@googlegroups.com>
Injection-Info: google-groups.googlegroups.com; posting-host=119.18.31.162; posting-account=zx0oKAkAAABMkjQR74hmeFvHN7cev7io
NNTP-Posting-Host: 119.18.31.162
References: <bc08c27f-cd5a-47e5-ac5c-1e014c590444n@googlegroups.com>
User-Agent: G2/1.0
MIME-Version: 1.0
Message-ID: <ff72eb5b-500b-443a-a5f3-531c9eff3c9en@googlegroups.com>
Subject: Re: osheet:cells(X,Y):=value=IF(a=b,c,if(...))
From: lawrence.edelstein@gmail.com (poopall)
Injection-Date: Tue, 11 Jan 2022 06:46:47 +0000
Content-Type: text/plain; charset="UTF-8"
Lines: 25
 by: poopall - Tue, 11 Jan 2022 06:46 UTC

On Tuesday, 11 January 2022 at 4:32:12 pm UTC+11, timec...@gmail.com wrote:
> how do i program/syntax:
>
> 1.
> for xloop=xrowstart to xrowend
>
> osheet:cells(xloop,16):value=opbal && P
> osheet:cells(xloop,17):value=debits && Q
> osheet:cells(xloop,18):value=credits && R
> osheet:cells(xloop,19):value="This Is The TaxCredit" && S
> osheet:cells(xloop,20):value="="+"P"+alltrim(str(xloop))+"-"+"Q"+alltrim(str(xloop))+"+"+"R"+alltrim(str(xloop)) && T
> *this is where i am stuck
> osheet:cells(xloop,21):value=if("T"+alltrim(str(xloop))>'0','cr',if("T"+alltrim(str(loop))='0',originalbalance,'dr')) && originalbalance is a predefined variable
>
> next
>
> 2. how to assign the value of a cell to a variable
> e.g.
> thetax=osheet:cells(xxx,yyy)
> @ rr,cc say thetax
>
> thanks

thetax := osheet:cells(xxx,yyy):value
@ rr,cc say thetax

Re: osheet:cells(X,Y):=value=IF(a=b,c,if(...))

<578f931d-22ac-41f5-b5c9-47f9214dee5bn@googlegroups.com>

  copy mid

https://www.rocksolidbbs.com/devel/article-flat.php?id=16917&group=comp.lang.xharbour#16917

  copy link   Newsgroups: comp.lang.xharbour
X-Received: by 2002:a05:620a:a4b:: with SMTP id j11mr2304996qka.561.1641884553369;
Mon, 10 Jan 2022 23:02:33 -0800 (PST)
X-Received: by 2002:ac8:7f8d:: with SMTP id z13mr2601140qtj.351.1641884553170;
Mon, 10 Jan 2022 23:02:33 -0800 (PST)
Path: i2pn2.org!i2pn.org!weretis.net!feeder6.news.weretis.net!news.misty.com!border2.nntp.dca1.giganews.com!nntp.giganews.com!news-out.google.com!nntp.google.com!postnews.google.com!google-groups.googlegroups.com!not-for-mail
Newsgroups: comp.lang.xharbour
Date: Mon, 10 Jan 2022 23:02:33 -0800 (PST)
In-Reply-To: <bc08c27f-cd5a-47e5-ac5c-1e014c590444n@googlegroups.com>
Injection-Info: google-groups.googlegroups.com; posting-host=119.18.31.162; posting-account=zx0oKAkAAABMkjQR74hmeFvHN7cev7io
NNTP-Posting-Host: 119.18.31.162
References: <bc08c27f-cd5a-47e5-ac5c-1e014c590444n@googlegroups.com>
User-Agent: G2/1.0
MIME-Version: 1.0
Message-ID: <578f931d-22ac-41f5-b5c9-47f9214dee5bn@googlegroups.com>
Subject: Re: osheet:cells(X,Y):=value=IF(a=b,c,if(...))
From: lawrence.edelstein@gmail.com (poopall)
Injection-Date: Tue, 11 Jan 2022 07:02:33 +0000
Content-Type: text/plain; charset="UTF-8"
Lines: 40
 by: poopall - Tue, 11 Jan 2022 07:02 UTC

On Tuesday, 11 January 2022 at 4:32:12 pm UTC+11, timec...@gmail.com wrote:
> how do i program/syntax:
>
> 1.
> for xloop=xrowstart to xrowend
>
> osheet:cells(xloop,16):value=opbal && P
> osheet:cells(xloop,17):value=debits && Q
> osheet:cells(xloop,18):value=credits && R
> osheet:cells(xloop,19):value="This Is The TaxCredit" && S
> osheet:cells(xloop,20):value="="+"P"+alltrim(str(xloop))+"-"+"Q"+alltrim(str(xloop))+"+"+"R"+alltrim(str(xloop)) && T
> *this is where i am stuck
> osheet:cells(xloop,21):value=if("T"+alltrim(str(xloop))>'0','cr',if("T"+alltrim(str(loop))='0',originalbalance,'dr')) && originalbalance is a predefined variable
>
> next
>
> 2. how to assign the value of a cell to a variable
> e.g.
> thetax=osheet:cells(xxx,yyy)
> @ rr,cc say thetax
>
> thanks

Firstly you are trying to compare a string to be greater than another string,
I was wondering what value you are expecting here ?, as I assume you want to compare it to a numeric value

Looks to me like you need to read the value first

xValue := oSheet:Range("T"+alltrim(str(xloop))
if xValue > 0
OutComment := "cr"
elseif xValue = 0
OutComment := "dr"
else
OutComment := "error" // <<maybe some other value here>>
endif

oSheet:cells(xLoop,21):Value = originalbalance
oSheet:cells(xLoop,21+1):value = OutComment

Re: osheet:cells(X,Y):=value=IF(a=b,c,if(...))

<8f2714fb-8999-458e-82ae-0997ec1f484fn@googlegroups.com>

  copy mid

https://www.rocksolidbbs.com/devel/article-flat.php?id=16918&group=comp.lang.xharbour#16918

  copy link   Newsgroups: comp.lang.xharbour
X-Received: by 2002:ac8:5a88:: with SMTP id c8mr2794145qtc.635.1641888956268;
Tue, 11 Jan 2022 00:15:56 -0800 (PST)
X-Received: by 2002:a05:622a:14a:: with SMTP id v10mr700110qtw.214.1641888956115;
Tue, 11 Jan 2022 00:15:56 -0800 (PST)
Path: i2pn2.org!i2pn.org!weretis.net!feeder8.news.weretis.net!border2.nntp.dca1.giganews.com!nntp.giganews.com!news-out.google.com!nntp.google.com!postnews.google.com!google-groups.googlegroups.com!not-for-mail
Newsgroups: comp.lang.xharbour
Date: Tue, 11 Jan 2022 00:15:55 -0800 (PST)
In-Reply-To: <578f931d-22ac-41f5-b5c9-47f9214dee5bn@googlegroups.com>
Injection-Info: google-groups.googlegroups.com; posting-host=2401:4900:1c20:6ad5:6431:9dcc:2b49:da87;
posting-account=jZAeNwoAAABYWjMRM-FfqKkHfC3Iph0l
NNTP-Posting-Host: 2401:4900:1c20:6ad5:6431:9dcc:2b49:da87
References: <bc08c27f-cd5a-47e5-ac5c-1e014c590444n@googlegroups.com> <578f931d-22ac-41f5-b5c9-47f9214dee5bn@googlegroups.com>
User-Agent: G2/1.0
MIME-Version: 1.0
Message-ID: <8f2714fb-8999-458e-82ae-0997ec1f484fn@googlegroups.com>
Subject: Re: osheet:cells(X,Y):=value=IF(a=b,c,if(...))
From: timecosting@gmail.com (timepro timesheet)
Injection-Date: Tue, 11 Jan 2022 08:15:56 +0000
Content-Type: text/plain; charset="UTF-8"
Lines: 58
 by: timepro timesheet - Tue, 11 Jan 2022 08:15 UTC

On Tuesday, January 11, 2022 at 12:32:34 PM UTC+5:30, poopall wrote:
> On Tuesday, 11 January 2022 at 4:32:12 pm UTC+11, timec...@gmail.com wrote:
> > how do i program/syntax:
> >
> > 1.
> > for xloop=xrowstart to xrowend
> >
> > osheet:cells(xloop,16):value=opbal && P
> > osheet:cells(xloop,17):value=debits && Q
> > osheet:cells(xloop,18):value=credits && R
> > osheet:cells(xloop,19):value="This Is The TaxCredit" && S
> > osheet:cells(xloop,20):value="="+"P"+alltrim(str(xloop))+"-"+"Q"+alltrim(str(xloop))+"+"+"R"+alltrim(str(xloop)) && T
> > *this is where i am stuck
> > osheet:cells(xloop,21):value=if("T"+alltrim(str(xloop))>'0','cr',if("T"+alltrim(str(loop))='0',originalbalance,'dr')) && originalbalance is a predefined variable
> >
> > next
> >
> > 2. how to assign the value of a cell to a variable
> > e.g.
> > thetax=osheet:cells(xxx,yyy)
> > @ rr,cc say thetax
> >
> > thanks
> Firstly you are trying to compare a string to be greater than another string,
> I was wondering what value you are expecting here ?, as I assume you want to compare it to a numeric value
>
> Looks to me like you need to read the value first
>
> xValue := oSheet:Range("T"+alltrim(str(xloop))
> if xValue > 0
> OutComment := "cr"
> elseif xValue = 0
> OutComment := "dr"
> else
> OutComment := "error" // <<maybe some other value here>>
> endif
>
> oSheet:cells(xLoop,21):Value = originalbalance
> oSheet:cells(xLoop,21+1):value = OutComment

thanks poopall:

'Firstly you are trying to compare a string to be greater than another string,
I was wondering what value you are expecting here ?, as I assume you want to compare it to a numeric value '
-that e.g. was just to get the syntax.

'thetax := osheet:cells(xxx,yyy):value'
-this helps me a lot.

in your e.g. xValue := oSheet:Range("T"+alltrim(str(xloop))
-just wondering why osheet:'Range' when i need the value from just a single cell?

can't i just code (inside the loop):
thetax := osheet:cells(xloop,20):value
oSheet:cells(xLoop,21):Value = thetax
....
....

Re: osheet:cells(X,Y):=value=IF(a=b,c,if(...))

<b7b0c064-ba25-400a-a88a-015a7365367an@googlegroups.com>

  copy mid

https://www.rocksolidbbs.com/devel/article-flat.php?id=16919&group=comp.lang.xharbour#16919

  copy link   Newsgroups: comp.lang.xharbour
X-Received: by 2002:ac8:5845:: with SMTP id h5mr2866039qth.365.1641891251510;
Tue, 11 Jan 2022 00:54:11 -0800 (PST)
X-Received: by 2002:a05:6214:2a8b:: with SMTP id jr11mr2761840qvb.49.1641891251399;
Tue, 11 Jan 2022 00:54:11 -0800 (PST)
Path: i2pn2.org!i2pn.org!weretis.net!feeder6.news.weretis.net!news.misty.com!border2.nntp.dca1.giganews.com!nntp.giganews.com!news-out.google.com!nntp.google.com!postnews.google.com!google-groups.googlegroups.com!not-for-mail
Newsgroups: comp.lang.xharbour
Date: Tue, 11 Jan 2022 00:54:11 -0800 (PST)
In-Reply-To: <8f2714fb-8999-458e-82ae-0997ec1f484fn@googlegroups.com>
Injection-Info: google-groups.googlegroups.com; posting-host=2401:4900:1c20:6ad5:6431:9dcc:2b49:da87;
posting-account=jZAeNwoAAABYWjMRM-FfqKkHfC3Iph0l
NNTP-Posting-Host: 2401:4900:1c20:6ad5:6431:9dcc:2b49:da87
References: <bc08c27f-cd5a-47e5-ac5c-1e014c590444n@googlegroups.com>
<578f931d-22ac-41f5-b5c9-47f9214dee5bn@googlegroups.com> <8f2714fb-8999-458e-82ae-0997ec1f484fn@googlegroups.com>
User-Agent: G2/1.0
MIME-Version: 1.0
Message-ID: <b7b0c064-ba25-400a-a88a-015a7365367an@googlegroups.com>
Subject: Re: osheet:cells(X,Y):=value=IF(a=b,c,if(...))
From: timecosting@gmail.com (timepro timesheet)
Injection-Date: Tue, 11 Jan 2022 08:54:11 +0000
Content-Type: text/plain; charset="UTF-8"
Lines: 69
 by: timepro timesheet - Tue, 11 Jan 2022 08:54 UTC

On Tuesday, January 11, 2022 at 1:45:56 PM UTC+5:30, timepro timesheet wrote:
> On Tuesday, January 11, 2022 at 12:32:34 PM UTC+5:30, poopall wrote:
> > On Tuesday, 11 January 2022 at 4:32:12 pm UTC+11, timec...@gmail.com wrote:
> > > how do i program/syntax:
> > >
> > > 1.
> > > for xloop=xrowstart to xrowend
> > >
> > > osheet:cells(xloop,16):value=opbal && P
> > > osheet:cells(xloop,17):value=debits && Q
> > > osheet:cells(xloop,18):value=credits && R
> > > osheet:cells(xloop,19):value="This Is The TaxCredit" && S
> > > osheet:cells(xloop,20):value="="+"P"+alltrim(str(xloop))+"-"+"Q"+alltrim(str(xloop))+"+"+"R"+alltrim(str(xloop)) && T
> > > *this is where i am stuck
> > > osheet:cells(xloop,21):value=if("T"+alltrim(str(xloop))>'0','cr',if("T"+alltrim(str(loop))='0',originalbalance,'dr')) && originalbalance is a predefined variable
> > >
> > > next
> > >
> > > 2. how to assign the value of a cell to a variable
> > > e.g.
> > > thetax=osheet:cells(xxx,yyy)
> > > @ rr,cc say thetax
> > >
> > > thanks
> > Firstly you are trying to compare a string to be greater than another string,
> > I was wondering what value you are expecting here ?, as I assume you want to compare it to a numeric value
> >
> > Looks to me like you need to read the value first
> >
> > xValue := oSheet:Range("T"+alltrim(str(xloop))
> > if xValue > 0
> > OutComment := "cr"
> > elseif xValue = 0
> > OutComment := "dr"
> > else
> > OutComment := "error" // <<maybe some other value here>>
> > endif
> >
> > oSheet:cells(xLoop,21):Value = originalbalance
> > oSheet:cells(xLoop,21+1):value = OutComment
> thanks poopall:
>
> 'Firstly you are trying to compare a string to be greater than another string,
> I was wondering what value you are expecting here ?, as I assume you want to compare it to a numeric value '
> -that e.g. was just to get the syntax.
>
> 'thetax := osheet:cells(xxx,yyy):value'
> -this helps me a lot.
>
> in your e.g. xValue := oSheet:Range("T"+alltrim(str(xloop))
> -just wondering why osheet:'Range' when i need the value from just a single cell?
>
> can't i just code (inside the loop):
> thetax := osheet:cells(xloop,20):value
> oSheet:cells(xLoop,21):Value = thetax
> ...
> ...
also:

what i am trying to achieve is this:

e.g. if cell(A824) is sum(A8..A823), then if any value in cells A8 - A823 is edited, the value in A824 "auto changes"...

for xxx=nstart to nend
how do i code cell(21,xxx) should "auto change" to 'paid' or 'non paid' or whatever...on the basis of cell(20,xxx) value.
(if value of cell(20,xxx)<0, then cell(21,xxx) must show 'paid', if cell(20,xxx)>0 then cell(21,xxx)...'unpaid'
-similar algorithm as sum()
next
so, if the user (on the open .xlsx file), 'manually' changes the value in cell(20,xxx) then 'correspondingly' cell(21,xxx) should auto change...
cell(21,xxx) has to be blocked for manual edit/input.

Re: osheet:cells(X,Y):=value=IF(a=b,c,if(...))

<84310c1c-ccc8-4bd4-8f74-1b494057d50an@googlegroups.com>

  copy mid

https://www.rocksolidbbs.com/devel/article-flat.php?id=16920&group=comp.lang.xharbour#16920

  copy link   Newsgroups: comp.lang.xharbour
X-Received: by 2002:a05:6214:2583:: with SMTP id fq3mr3119020qvb.94.1641897232010;
Tue, 11 Jan 2022 02:33:52 -0800 (PST)
X-Received: by 2002:ad4:5aac:: with SMTP id u12mr3123234qvg.8.1641897231874;
Tue, 11 Jan 2022 02:33:51 -0800 (PST)
Path: i2pn2.org!i2pn.org!weretis.net!feeder6.news.weretis.net!news.misty.com!border2.nntp.dca1.giganews.com!nntp.giganews.com!news-out.google.com!nntp.google.com!postnews.google.com!google-groups.googlegroups.com!not-for-mail
Newsgroups: comp.lang.xharbour
Date: Tue, 11 Jan 2022 02:33:51 -0800 (PST)
In-Reply-To: <b7b0c064-ba25-400a-a88a-015a7365367an@googlegroups.com>
Injection-Info: google-groups.googlegroups.com; posting-host=2401:4900:1c20:6ad5:6431:9dcc:2b49:da87;
posting-account=jZAeNwoAAABYWjMRM-FfqKkHfC3Iph0l
NNTP-Posting-Host: 2401:4900:1c20:6ad5:6431:9dcc:2b49:da87
References: <bc08c27f-cd5a-47e5-ac5c-1e014c590444n@googlegroups.com>
<578f931d-22ac-41f5-b5c9-47f9214dee5bn@googlegroups.com> <8f2714fb-8999-458e-82ae-0997ec1f484fn@googlegroups.com>
<b7b0c064-ba25-400a-a88a-015a7365367an@googlegroups.com>
User-Agent: G2/1.0
MIME-Version: 1.0
Message-ID: <84310c1c-ccc8-4bd4-8f74-1b494057d50an@googlegroups.com>
Subject: Re: osheet:cells(X,Y):=value=IF(a=b,c,if(...))
From: timecosting@gmail.com (timepro timesheet)
Injection-Date: Tue, 11 Jan 2022 10:33:51 +0000
Content-Type: text/plain; charset="UTF-8"
Lines: 78
 by: timepro timesheet - Tue, 11 Jan 2022 10:33 UTC

On Tuesday, January 11, 2022 at 2:24:12 PM UTC+5:30, timepro timesheet wrote:
> On Tuesday, January 11, 2022 at 1:45:56 PM UTC+5:30, timepro timesheet wrote:
> > On Tuesday, January 11, 2022 at 12:32:34 PM UTC+5:30, poopall wrote:
> > > On Tuesday, 11 January 2022 at 4:32:12 pm UTC+11, timec...@gmail.com wrote:
> > > > how do i program/syntax:
> > > >
> > > > 1.
> > > > for xloop=xrowstart to xrowend
> > > >
> > > > osheet:cells(xloop,16):value=opbal && P
> > > > osheet:cells(xloop,17):value=debits && Q
> > > > osheet:cells(xloop,18):value=credits && R
> > > > osheet:cells(xloop,19):value="This Is The TaxCredit" && S
> > > > osheet:cells(xloop,20):value="="+"P"+alltrim(str(xloop))+"-"+"Q"+alltrim(str(xloop))+"+"+"R"+alltrim(str(xloop)) && T
> > > > *this is where i am stuck
> > > > osheet:cells(xloop,21):value=if("T"+alltrim(str(xloop))>'0','cr',if("T"+alltrim(str(loop))='0',originalbalance,'dr')) && originalbalance is a predefined variable
> > > >
> > > > next
> > > >
> > > > 2. how to assign the value of a cell to a variable
> > > > e.g.
> > > > thetax=osheet:cells(xxx,yyy)
> > > > @ rr,cc say thetax
> > > >
> > > > thanks
> > > Firstly you are trying to compare a string to be greater than another string,
> > > I was wondering what value you are expecting here ?, as I assume you want to compare it to a numeric value
> > >
> > > Looks to me like you need to read the value first
> > >
> > > xValue := oSheet:Range("T"+alltrim(str(xloop))
> > > if xValue > 0
> > > OutComment := "cr"
> > > elseif xValue = 0
> > > OutComment := "dr"
> > > else
> > > OutComment := "error" // <<maybe some other value here>>
> > > endif
> > >
> > > oSheet:cells(xLoop,21):Value = originalbalance
> > > oSheet:cells(xLoop,21+1):value = OutComment
> > thanks poopall:
> >
> > 'Firstly you are trying to compare a string to be greater than another string,
> > I was wondering what value you are expecting here ?, as I assume you want to compare it to a numeric value '
> > -that e.g. was just to get the syntax.
> >
> > 'thetax := osheet:cells(xxx,yyy):value'
> > -this helps me a lot.
> >
> > in your e.g. xValue := oSheet:Range("T"+alltrim(str(xloop))
> > -just wondering why osheet:'Range' when i need the value from just a single cell?
> >
> > can't i just code (inside the loop):
> > thetax := osheet:cells(xloop,20):value
> > oSheet:cells(xLoop,21):Value = thetax
> > ...
> > ...
> also:
>
> what i am trying to achieve is this:
>
> e.g. if cell(A824) is sum(A8..A823), then if any value in cells A8 - A823 is edited, the value in A824 "auto changes"...
>
> for xxx=nstart to nend
> how do i code cell(21,xxx) should "auto change" to 'paid' or 'non paid' or whatever...on the basis of cell(20,xxx) value.
> (if value of cell(20,xxx)<0, then cell(21,xxx) must show 'paid', if cell(20,xxx)>0 then cell(21,xxx)...'unpaid'
> -similar algorithm as sum()
> next
> so, if the user (on the open .xlsx file), 'manually' changes the value in cell(20,xxx) then 'correspondingly' cell(21,xxx) should auto change...
> cell(21,xxx) has to be blocked for manual edit/input.

how to use a variable with/in sum() or formula()

a1="E"+alltrim(str(xrow,6))
a2="J"+alltrim(str(xrow,6));oSheet:cells(xrow,20)
a3="N"+alltrim(str(xrow,6));oSheet:cells(xrow,20)
:Formula = "=((&a1+&a2)/(&a3))*taxpercent" && in process it does not pick the value of taxpercent (can be diff. for each year)

Re: osheet:cells(X,Y):=value=IF(a=b,c,if(...))

<0a264877-bd72-4178-aeae-ad99b2e9f585n@googlegroups.com>

  copy mid

https://www.rocksolidbbs.com/devel/article-flat.php?id=16922&group=comp.lang.xharbour#16922

  copy link   Newsgroups: comp.lang.xharbour
X-Received: by 2002:a05:622a:13d1:: with SMTP id p17mr5920696qtk.386.1642120590390;
Thu, 13 Jan 2022 16:36:30 -0800 (PST)
X-Received: by 2002:ac8:5d93:: with SMTP id d19mr5854460qtx.191.1642120590087;
Thu, 13 Jan 2022 16:36:30 -0800 (PST)
Path: i2pn2.org!i2pn.org!weretis.net!feeder6.news.weretis.net!news.misty.com!border2.nntp.dca1.giganews.com!nntp.giganews.com!news-out.google.com!nntp.google.com!postnews.google.com!google-groups.googlegroups.com!not-for-mail
Newsgroups: comp.lang.xharbour
Date: Thu, 13 Jan 2022 16:36:29 -0800 (PST)
In-Reply-To: <8f2714fb-8999-458e-82ae-0997ec1f484fn@googlegroups.com>
Injection-Info: google-groups.googlegroups.com; posting-host=14.202.219.18; posting-account=zx0oKAkAAABMkjQR74hmeFvHN7cev7io
NNTP-Posting-Host: 14.202.219.18
References: <bc08c27f-cd5a-47e5-ac5c-1e014c590444n@googlegroups.com>
<578f931d-22ac-41f5-b5c9-47f9214dee5bn@googlegroups.com> <8f2714fb-8999-458e-82ae-0997ec1f484fn@googlegroups.com>
User-Agent: G2/1.0
MIME-Version: 1.0
Message-ID: <0a264877-bd72-4178-aeae-ad99b2e9f585n@googlegroups.com>
Subject: Re: osheet:cells(X,Y):=value=IF(a=b,c,if(...))
From: lawrence.edelstein@gmail.com (poopall)
Injection-Date: Fri, 14 Jan 2022 00:36:30 +0000
Content-Type: text/plain; charset="UTF-8"
Lines: 58
 by: poopall - Fri, 14 Jan 2022 00:36 UTC

On Tuesday, 11 January 2022 at 7:15:56 pm UTC+11, timec...@gmail.com wrote:
> On Tuesday, January 11, 2022 at 12:32:34 PM UTC+5:30, poopall wrote:
> > On Tuesday, 11 January 2022 at 4:32:12 pm UTC+11, timec...@gmail.com wrote:
> > > how do i program/syntax:
> > >
> > > 1.
> > > for xloop=xrowstart to xrowend
> > >
> > > osheet:cells(xloop,16):value=opbal && P
> > > osheet:cells(xloop,17):value=debits && Q
> > > osheet:cells(xloop,18):value=credits && R
> > > osheet:cells(xloop,19):value="This Is The TaxCredit" && S
> > > osheet:cells(xloop,20):value="="+"P"+alltrim(str(xloop))+"-"+"Q"+alltrim(str(xloop))+"+"+"R"+alltrim(str(xloop)) && T
> > > *this is where i am stuck
> > > osheet:cells(xloop,21):value=if("T"+alltrim(str(xloop))>'0','cr',if("T"+alltrim(str(loop))='0',originalbalance,'dr')) && originalbalance is a predefined variable
> > >
> > > next
> > >
> > > 2. how to assign the value of a cell to a variable
> > > e.g.
> > > thetax=osheet:cells(xxx,yyy)
> > > @ rr,cc say thetax
> > >
> > > thanks
> > Firstly you are trying to compare a string to be greater than another string,
> > I was wondering what value you are expecting here ?, as I assume you want to compare it to a numeric value
> >
> > Looks to me like you need to read the value first
> >
> > xValue := oSheet:Range("T"+alltrim(str(xloop))
> > if xValue > 0
> > OutComment := "cr"
> > elseif xValue = 0
> > OutComment := "dr"
> > else
> > OutComment := "error" // <<maybe some other value here>>
> > endif
> >
> > oSheet:cells(xLoop,21):Value = originalbalance
> > oSheet:cells(xLoop,21+1):value = OutComment
> thanks poopall:
>
> 'Firstly you are trying to compare a string to be greater than another string,
> I was wondering what value you are expecting here ?, as I assume you want to compare it to a numeric value '
> -that e.g. was just to get the syntax.
>
> 'thetax := osheet:cells(xxx,yyy):value'
> -this helps me a lot.
>
> in your e.g. xValue := oSheet:Range("T"+alltrim(str(xloop))
> -just wondering why osheet:'Range' when i need the value from just a single cell?
>
> can't i just code (inside the loop):
> thetax := osheet:cells(xloop,20):value
> oSheet:cells(xLoop,21):Value = thetax
> ...
> ...

A range can be a single cell, so use oSheet:Range("T"+alltrim(str(xloop)) tro extract the value

Re: osheet:cells(X,Y):=value=IF(a=b,c,if(...))

<51d6682a-6485-4cb8-9731-809f29fd740dn@googlegroups.com>

  copy mid

https://www.rocksolidbbs.com/devel/article-flat.php?id=16923&group=comp.lang.xharbour#16923

  copy link   Newsgroups: comp.lang.xharbour
X-Received: by 2002:a05:622a:5ce:: with SMTP id d14mr5867545qtb.412.1642121747586;
Thu, 13 Jan 2022 16:55:47 -0800 (PST)
X-Received: by 2002:a37:a64e:: with SMTP id p75mr367101qke.166.1642121747354;
Thu, 13 Jan 2022 16:55:47 -0800 (PST)
Path: i2pn2.org!i2pn.org!weretis.net!feeder6.news.weretis.net!news.misty.com!border2.nntp.dca1.giganews.com!nntp.giganews.com!news-out.google.com!nntp.google.com!postnews.google.com!google-groups.googlegroups.com!not-for-mail
Newsgroups: comp.lang.xharbour
Date: Thu, 13 Jan 2022 16:55:47 -0800 (PST)
In-Reply-To: <b7b0c064-ba25-400a-a88a-015a7365367an@googlegroups.com>
Injection-Info: google-groups.googlegroups.com; posting-host=14.202.219.18; posting-account=zx0oKAkAAABMkjQR74hmeFvHN7cev7io
NNTP-Posting-Host: 14.202.219.18
References: <bc08c27f-cd5a-47e5-ac5c-1e014c590444n@googlegroups.com>
<578f931d-22ac-41f5-b5c9-47f9214dee5bn@googlegroups.com> <8f2714fb-8999-458e-82ae-0997ec1f484fn@googlegroups.com>
<b7b0c064-ba25-400a-a88a-015a7365367an@googlegroups.com>
User-Agent: G2/1.0
MIME-Version: 1.0
Message-ID: <51d6682a-6485-4cb8-9731-809f29fd740dn@googlegroups.com>
Subject: Re: osheet:cells(X,Y):=value=IF(a=b,c,if(...))
From: lawrence.edelstein@gmail.com (poopall)
Injection-Date: Fri, 14 Jan 2022 00:55:47 +0000
Content-Type: text/plain; charset="UTF-8"
Lines: 82
 by: poopall - Fri, 14 Jan 2022 00:55 UTC

On Tuesday, 11 January 2022 at 7:54:12 pm UTC+11, timec...@gmail.com wrote:
> On Tuesday, January 11, 2022 at 1:45:56 PM UTC+5:30, timepro timesheet wrote:
> > On Tuesday, January 11, 2022 at 12:32:34 PM UTC+5:30, poopall wrote:
> > > On Tuesday, 11 January 2022 at 4:32:12 pm UTC+11, timec...@gmail.com wrote:
> > > > how do i program/syntax:
> > > >
> > > > 1.
> > > > for xloop=xrowstart to xrowend
> > > >
> > > > osheet:cells(xloop,16):value=opbal && P
> > > > osheet:cells(xloop,17):value=debits && Q
> > > > osheet:cells(xloop,18):value=credits && R
> > > > osheet:cells(xloop,19):value="This Is The TaxCredit" && S
> > > > osheet:cells(xloop,20):value="="+"P"+alltrim(str(xloop))+"-"+"Q"+alltrim(str(xloop))+"+"+"R"+alltrim(str(xloop)) && T
> > > > *this is where i am stuck
> > > > osheet:cells(xloop,21):value=if("T"+alltrim(str(xloop))>'0','cr',if("T"+alltrim(str(loop))='0',originalbalance,'dr')) && originalbalance is a predefined variable
> > > >
> > > > next
> > > >
> > > > 2. how to assign the value of a cell to a variable
> > > > e.g.
> > > > thetax=osheet:cells(xxx,yyy)
> > > > @ rr,cc say thetax
> > > >
> > > > thanks
> > > Firstly you are trying to compare a string to be greater than another string,
> > > I was wondering what value you are expecting here ?, as I assume you want to compare it to a numeric value
> > >
> > > Looks to me like you need to read the value first
> > >
> > > xValue := oSheet:Range("T"+alltrim(str(xloop))
> > > if xValue > 0
> > > OutComment := "cr"
> > > elseif xValue = 0
> > > OutComment := "dr"
> > > else
> > > OutComment := "error" // <<maybe some other value here>>
> > > endif
> > >
> > > oSheet:cells(xLoop,21):Value = originalbalance
> > > oSheet:cells(xLoop,21+1):value = OutComment
> > thanks poopall:
> >
> > 'Firstly you are trying to compare a string to be greater than another string,
> > I was wondering what value you are expecting here ?, as I assume you want to compare it to a numeric value '
> > -that e.g. was just to get the syntax.
> >
> > 'thetax := osheet:cells(xxx,yyy):value'
> > -this helps me a lot.
> >
> > in your e.g. xValue := oSheet:Range("T"+alltrim(str(xloop))
> > -just wondering why osheet:'Range' when i need the value from just a single cell?
> >
> > can't i just code (inside the loop):
> > thetax := osheet:cells(xloop,20):value
> > oSheet:cells(xLoop,21):Value = thetax
> > ...
> > ...
> also:
>
> what i am trying to achieve is this:
>
> e.g. if cell(A824) is sum(A8..A823), then if any value in cells A8 - A823 is edited, the value in A824 "auto changes"...
>
> for xxx=nstart to nend
> how do i code cell(21,xxx) should "auto change" to 'paid' or 'non paid' or whatever...on the basis of cell(20,xxx) value.
> (if value of cell(20,xxx)<0, then cell(21,xxx) must show 'paid', if cell(20,xxx)>0 then cell(21,xxx)...'unpaid'
> -similar algorithm as sum()
> next
> so, if the user (on the open .xlsx file), 'manually' changes the value in cell(20,xxx) then 'correspondingly' cell(21,xxx) should auto change...
> cell(21,xxx) has to be blocked for manual edit/input.

Use the sum formula in a824, excel will calculate any changes
oSheet:Cells( "A824" ):Formula = "=sum(a8:A823)", I am sure that I have mentioned this in previous posts

how do i code cell(21,xxx) should "auto change" to 'paid' or 'non paid' or whatever...on the basis of cell(20,xxx) value.

I think you are trying to learn the basic of Excel and Harbour at the same time

oSheet:Cells( 21,xxx ):Formula = "=if({write your excel formula here} )"

cell(21,xxx) has to be blocked for manual edit/input.
Would require you to protect the spreadsheet with a password, and then unlock the cell..range eg 21,xxx with a password.. I have not tried this programmatically within xharbour so cant assist.

Re: osheet:cells(X,Y):=value=IF(a=b,c,if(...))

<04dcf94d-d9e0-4c72-b9fd-5c4b36445cd2n@googlegroups.com>

  copy mid

https://www.rocksolidbbs.com/devel/article-flat.php?id=16924&group=comp.lang.xharbour#16924

  copy link   Newsgroups: comp.lang.xharbour
X-Received: by 2002:ad4:4ee7:: with SMTP id dv7mr7012743qvb.48.1642139238465;
Thu, 13 Jan 2022 21:47:18 -0800 (PST)
X-Received: by 2002:ac8:5a11:: with SMTP id n17mr6581435qta.26.1642139238311;
Thu, 13 Jan 2022 21:47:18 -0800 (PST)
Path: i2pn2.org!i2pn.org!weretis.net!feeder6.news.weretis.net!news.misty.com!border2.nntp.dca1.giganews.com!border1.nntp.dca1.giganews.com!nntp.giganews.com!news-out.google.com!nntp.google.com!postnews.google.com!google-groups.googlegroups.com!not-for-mail
Newsgroups: comp.lang.xharbour
Date: Thu, 13 Jan 2022 21:47:18 -0800 (PST)
In-Reply-To: <51d6682a-6485-4cb8-9731-809f29fd740dn@googlegroups.com>
Injection-Info: google-groups.googlegroups.com; posting-host=2401:4900:1c20:6ad5:6ce2:9819:7b33:ef0;
posting-account=TQ3r0woAAABpjOD3G10Mm_gh_OqPDkPo
NNTP-Posting-Host: 2401:4900:1c20:6ad5:6ce2:9819:7b33:ef0
References: <bc08c27f-cd5a-47e5-ac5c-1e014c590444n@googlegroups.com>
<578f931d-22ac-41f5-b5c9-47f9214dee5bn@googlegroups.com> <8f2714fb-8999-458e-82ae-0997ec1f484fn@googlegroups.com>
<b7b0c064-ba25-400a-a88a-015a7365367an@googlegroups.com> <51d6682a-6485-4cb8-9731-809f29fd740dn@googlegroups.com>
User-Agent: G2/1.0
MIME-Version: 1.0
Message-ID: <04dcf94d-d9e0-4c72-b9fd-5c4b36445cd2n@googlegroups.com>
Subject: Re: osheet:cells(X,Y):=value=IF(a=b,c,if(...))
From: valiapareshn@gmail.com (PARESH VALIA)
Injection-Date: Fri, 14 Jan 2022 05:47:18 +0000
Content-Type: text/plain; charset="UTF-8"
Lines: 16
 by: PARESH VALIA - Fri, 14 Jan 2022 05:47 UTC

thanks poopall:

'I think you are trying to learn the basic of Excel and Harbour at the same time '
-pretty much...

' oSheet:Cells( 21,xxx ):Formula = "=if({write your excel formula here} )"'
-tried oSheet:Cells( 21,xrow ):Formula = "=if({'P'+alltrim(str(xow))>0, 'unpaid',if('P'+alltrim(str(xrow))=0, 'paid', 'cr.bal'))} )"
doe not work. (is my coding/syntax wrong)

also,
how to use a VARIABLE with/in sum() or formula()
a1="E"+alltrim(str(xrow))
a2="J"+alltrim(str(xrow))
a3="N"+alltrim(str(xrow))
:Formula = "=((&a1+&a2)/(&a3))*nTAXPERCENT" && processing, it does not pick the value of ntaxpercent (can be same/diff. for each cell - on the basis of the record date)

Re: osheet:cells(X,Y):=value=IF(a=b,c,if(...))

<dc326587-b0d4-48d2-be1e-718bdc4a48b2n@googlegroups.com>

  copy mid

https://www.rocksolidbbs.com/devel/article-flat.php?id=16925&group=comp.lang.xharbour#16925

  copy link   Newsgroups: comp.lang.xharbour
X-Received: by 2002:ac8:5845:: with SMTP id h5mr6719741qth.365.1642142237466;
Thu, 13 Jan 2022 22:37:17 -0800 (PST)
X-Received: by 2002:ac8:4142:: with SMTP id e2mr6549419qtm.603.1642142237200;
Thu, 13 Jan 2022 22:37:17 -0800 (PST)
Path: i2pn2.org!i2pn.org!weretis.net!feeder6.news.weretis.net!news.misty.com!border2.nntp.dca1.giganews.com!border1.nntp.dca1.giganews.com!nntp.giganews.com!news-out.google.com!nntp.google.com!postnews.google.com!google-groups.googlegroups.com!not-for-mail
Newsgroups: comp.lang.xharbour
Date: Thu, 13 Jan 2022 22:37:16 -0800 (PST)
In-Reply-To: <04dcf94d-d9e0-4c72-b9fd-5c4b36445cd2n@googlegroups.com>
Injection-Info: google-groups.googlegroups.com; posting-host=119.18.31.162; posting-account=zx0oKAkAAABMkjQR74hmeFvHN7cev7io
NNTP-Posting-Host: 119.18.31.162
References: <bc08c27f-cd5a-47e5-ac5c-1e014c590444n@googlegroups.com>
<578f931d-22ac-41f5-b5c9-47f9214dee5bn@googlegroups.com> <8f2714fb-8999-458e-82ae-0997ec1f484fn@googlegroups.com>
<b7b0c064-ba25-400a-a88a-015a7365367an@googlegroups.com> <51d6682a-6485-4cb8-9731-809f29fd740dn@googlegroups.com>
<04dcf94d-d9e0-4c72-b9fd-5c4b36445cd2n@googlegroups.com>
User-Agent: G2/1.0
MIME-Version: 1.0
Message-ID: <dc326587-b0d4-48d2-be1e-718bdc4a48b2n@googlegroups.com>
Subject: Re: osheet:cells(X,Y):=value=IF(a=b,c,if(...))
From: lawrence.edelstein@gmail.com (poopall)
Injection-Date: Fri, 14 Jan 2022 06:37:17 +0000
Content-Type: text/plain; charset="UTF-8"
Lines: 34
 by: poopall - Fri, 14 Jan 2022 06:37 UTC

On Friday, 14 January 2022 at 4:47:19 pm UTC+11, PARESH VALIA wrote:
> thanks poopall:
>
> 'I think you are trying to learn the basic of Excel and Harbour at the same time '
> -pretty much...
>
> ' oSheet:Cells( 21,xxx ):Formula = "=if({write your excel formula here} )"'
> -tried oSheet:Cells( 21,xrow ):Formula = "=if({'P'+alltrim(str(xow))>0, 'unpaid',if('P'+alltrim(str(xrow))=0, 'paid', 'cr.bal'))} )"
> doe not work. (is my coding/syntax wrong)
>
> also,
> how to use a VARIABLE with/in sum() or formula()
> a1="E"+alltrim(str(xrow))
> a2="J"+alltrim(str(xrow))
> a3="N"+alltrim(str(xrow))
> :Formula = "=((&a1+&a2)/(&a3))*nTAXPERCENT" && processing, it does not pick the value of ntaxpercent (can be same/diff. for each cell - on the basis of the record date)

"=if({'P'+alltrim(str(xow))>0, 'unpaid',if('P'+alltrim(str(xrow))=0, 'paid', 'cr.bal'))} )

Do this
oSheet:Cells( 21,xrow ):Formula = '=if(P' +alltrim(str(xow))+'>0, "unpaid" ,if (P'+alltrim(str(xrow))+'=0, "paid", "cr.bal"))'

I hav'nt tested so cant guarantee it will work but you need to build the xHarbour string to include what you need for the Excel formulae

Formula = "=((&a1+&a2)/(&a3))*nTAXPERCENT"

This is not an excel formula you so can't substitute XHarbour code into Excel code and visa versa

Formula = "=(("+a1+"+"+a2+")/("+a3+"))*nTAXPERCENT"

I assume nTAXPERCENT is referring to a named Excel range (single cell) which is pre-defined elsewhere in your speadsheet.

Re: osheet:cells(X,Y):=value=IF(a=b,c,if(...))

<69231554-0808-414d-a5d7-20718a568e27n@googlegroups.com>

  copy mid

https://www.rocksolidbbs.com/devel/article-flat.php?id=16926&group=comp.lang.xharbour#16926

  copy link   Newsgroups: comp.lang.xharbour
X-Received: by 2002:a05:622a:242:: with SMTP id c2mr6535062qtx.559.1642146271650;
Thu, 13 Jan 2022 23:44:31 -0800 (PST)
X-Received: by 2002:a05:6214:76e:: with SMTP id f14mr7299672qvz.43.1642146271519;
Thu, 13 Jan 2022 23:44:31 -0800 (PST)
Path: i2pn2.org!i2pn.org!weretis.net!feeder6.news.weretis.net!news.misty.com!border2.nntp.dca1.giganews.com!nntp.giganews.com!news-out.google.com!nntp.google.com!postnews.google.com!google-groups.googlegroups.com!not-for-mail
Newsgroups: comp.lang.xharbour
Date: Thu, 13 Jan 2022 23:44:31 -0800 (PST)
In-Reply-To: <dc326587-b0d4-48d2-be1e-718bdc4a48b2n@googlegroups.com>
Injection-Info: google-groups.googlegroups.com; posting-host=2401:4900:1c20:6ad5:6ce2:9819:7b33:ef0;
posting-account=TQ3r0woAAABpjOD3G10Mm_gh_OqPDkPo
NNTP-Posting-Host: 2401:4900:1c20:6ad5:6ce2:9819:7b33:ef0
References: <bc08c27f-cd5a-47e5-ac5c-1e014c590444n@googlegroups.com>
<578f931d-22ac-41f5-b5c9-47f9214dee5bn@googlegroups.com> <8f2714fb-8999-458e-82ae-0997ec1f484fn@googlegroups.com>
<b7b0c064-ba25-400a-a88a-015a7365367an@googlegroups.com> <51d6682a-6485-4cb8-9731-809f29fd740dn@googlegroups.com>
<04dcf94d-d9e0-4c72-b9fd-5c4b36445cd2n@googlegroups.com> <dc326587-b0d4-48d2-be1e-718bdc4a48b2n@googlegroups.com>
User-Agent: G2/1.0
MIME-Version: 1.0
Message-ID: <69231554-0808-414d-a5d7-20718a568e27n@googlegroups.com>
Subject: Re: osheet:cells(X,Y):=value=IF(a=b,c,if(...))
From: valiapareshn@gmail.com (PARESH VALIA)
Injection-Date: Fri, 14 Jan 2022 07:44:31 +0000
Content-Type: text/plain; charset="UTF-8"
Lines: 53
 by: PARESH VALIA - Fri, 14 Jan 2022 07:44 UTC

On Friday, 14 January 2022 at 12:07:18 UTC+5:30, poopall wrote:
> On Friday, 14 January 2022 at 4:47:19 pm UTC+11, PARESH VALIA wrote:
> > thanks poopall:
> >
> > 'I think you are trying to learn the basic of Excel and Harbour at the same time '
> > -pretty much...
> >
> > ' oSheet:Cells( 21,xxx ):Formula = "=if({write your excel formula here} )"'
> > -tried oSheet:Cells( 21,xrow ):Formula = "=if({'P'+alltrim(str(xow))>0, 'unpaid',if('P'+alltrim(str(xrow))=0, 'paid', 'cr.bal'))} )"
> > doe not work. (is my coding/syntax wrong)
> >
> > also,
> > how to use a VARIABLE with/in sum() or formula()
> > a1="E"+alltrim(str(xrow))
> > a2="J"+alltrim(str(xrow))
> > a3="N"+alltrim(str(xrow))
> > :Formula = "=((&a1+&a2)/(&a3))*nTAXPERCENT" && processing, it does not pick the value of ntaxpercent (can be same/diff. for each cell - on the basis of the record date)
>
>
> "=if({'P'+alltrim(str(xow))>0, 'unpaid',if('P'+alltrim(str(xrow))=0, 'paid', 'cr.bal'))} )
> Do this
> oSheet:Cells( 21,xrow ):Formula = '=if(P' +alltrim(str(xow))+'>0, "unpaid" ,if (P'+alltrim(str(xrow))+'=0, "paid", "cr.bal"))'
>
> I hav'nt tested so cant guarantee it will work but you need to build the xHarbour string to include what you need for the Excel formulae
>
> Formula = "=((&a1+&a2)/(&a3))*nTAXPERCENT"
>
> This is not an excel formula you so can't substitute XHarbour code into Excel code and visa versa
>
> Formula = "=(("+a1+"+"+a2+")/("+a3+"))*nTAXPERCENT"
>
> I assume nTAXPERCENT is referring to a named Excel range (single cell) which is pre-defined elsewhere in your speadsheet.

thanks again poopall:

'oSheet:Cells( 21,xrow ):Formula = '=if(P' +alltrim(str(xow))+'>0, "unpaid" ,if (P'+alltrim(str(xrow))+'=0, "paid", "cr.bal"))'
--had tried that too...but doesn't work.

' I assume nTAXPERCENT is referring to a named Excel range (single cell) which is pre-defined elsewhere in your speadsheet.'
Formula = "=(("+a1+"+"+a2+")/("+a3+"))*nTAXPERCENT"
--no, it is initially defined as a public variable in main.prg. then, in the do whil !eo()...skip...endd, ntaxpercent value may vary on the basis of the 'billyear field' inside the loop after each skip.

in the loop
if field->billyear=2017; ntaxpercent=field->billtaxperc
ntaxpercent is 15 - so, cell('P'+str(xrow++)) Formula = "=(("+a1+"+"+a2+")/("+a3+"))*15"
skip
if field->billyear=2015; ntaxpercent=field->billtaxperc
ntaxpercent is 18 - so, cell('P'+str(xrow++)) Formula = "=(("+a1+"+"+a2+")/("+a3+"))*18"
skip
if field->billyear=2021; ntaxpercent=field->billtaxperc
ntaxpercent is nXYZ - so, cell('P'+str(xrow++)) Formula = "=(("+a1+"+"+a2+")/("+a3+"))*nXYZ
skip
....

Re: osheet:cells(X,Y):=value=IF(a=b,c,if(...))

<f969e002-7e1d-4e96-8092-c3c524f9f00fn@googlegroups.com>

  copy mid

https://www.rocksolidbbs.com/devel/article-flat.php?id=16927&group=comp.lang.xharbour#16927

  copy link   Newsgroups: comp.lang.xharbour
X-Received: by 2002:a37:452:: with SMTP id 79mr82115qke.561.1642147295871;
Fri, 14 Jan 2022 00:01:35 -0800 (PST)
X-Received: by 2002:a05:6214:2b05:: with SMTP id jx5mr6988486qvb.124.1642147295744;
Fri, 14 Jan 2022 00:01:35 -0800 (PST)
Path: i2pn2.org!i2pn.org!news.swapon.de!weretis.net!feeder6.news.weretis.net!news.misty.com!border2.nntp.dca1.giganews.com!nntp.giganews.com!news-out.google.com!nntp.google.com!postnews.google.com!google-groups.googlegroups.com!not-for-mail
Newsgroups: comp.lang.xharbour
Date: Fri, 14 Jan 2022 00:01:35 -0800 (PST)
In-Reply-To: <dc326587-b0d4-48d2-be1e-718bdc4a48b2n@googlegroups.com>
Injection-Info: google-groups.googlegroups.com; posting-host=2401:4900:1c20:6ad5:6ce2:9819:7b33:ef0;
posting-account=jZAeNwoAAABYWjMRM-FfqKkHfC3Iph0l
NNTP-Posting-Host: 2401:4900:1c20:6ad5:6ce2:9819:7b33:ef0
References: <bc08c27f-cd5a-47e5-ac5c-1e014c590444n@googlegroups.com>
<578f931d-22ac-41f5-b5c9-47f9214dee5bn@googlegroups.com> <8f2714fb-8999-458e-82ae-0997ec1f484fn@googlegroups.com>
<b7b0c064-ba25-400a-a88a-015a7365367an@googlegroups.com> <51d6682a-6485-4cb8-9731-809f29fd740dn@googlegroups.com>
<04dcf94d-d9e0-4c72-b9fd-5c4b36445cd2n@googlegroups.com> <dc326587-b0d4-48d2-be1e-718bdc4a48b2n@googlegroups.com>
User-Agent: G2/1.0
MIME-Version: 1.0
Message-ID: <f969e002-7e1d-4e96-8092-c3c524f9f00fn@googlegroups.com>
Subject: Re: osheet:cells(X,Y):=value=IF(a=b,c,if(...))
From: timecosting@gmail.com (timepro timesheet)
Injection-Date: Fri, 14 Jan 2022 08:01:35 +0000
Content-Type: text/plain; charset="UTF-8"
Lines: 53
 by: timepro timesheet - Fri, 14 Jan 2022 08:01 UTC

On Friday, January 14, 2022 at 12:07:18 PM UTC+5:30, poopall wrote:
> On Friday, 14 January 2022 at 4:47:19 pm UTC+11, PARESH VALIA wrote:
> > thanks poopall:
> >
> > 'I think you are trying to learn the basic of Excel and Harbour at the same time '
> > -pretty much...
> >
> > ' oSheet:Cells( 21,xxx ):Formula = "=if({write your excel formula here} )"'
> > -tried oSheet:Cells( 21,xrow ):Formula = "=if({'P'+alltrim(str(xow))>0, 'unpaid',if('P'+alltrim(str(xrow))=0, 'paid', 'cr.bal'))} )"
> > doe not work. (is my coding/syntax wrong)
> >
> > also,
> > how to use a VARIABLE with/in sum() or formula()
> > a1="E"+alltrim(str(xrow))
> > a2="J"+alltrim(str(xrow))
> > a3="N"+alltrim(str(xrow))
> > :Formula = "=((&a1+&a2)/(&a3))*nTAXPERCENT" && processing, it does not pick the value of ntaxpercent (can be same/diff. for each cell - on the basis of the record date)
>
>
> "=if({'P'+alltrim(str(xow))>0, 'unpaid',if('P'+alltrim(str(xrow))=0, 'paid', 'cr.bal'))} )
> Do this
> oSheet:Cells( 21,xrow ):Formula = '=if(P' +alltrim(str(xow))+'>0, "unpaid" ,if (P'+alltrim(str(xrow))+'=0, "paid", "cr.bal"))'
>
> I hav'nt tested so cant guarantee it will work but you need to build the xHarbour string to include what you need for the Excel formulae
>
> Formula = "=((&a1+&a2)/(&a3))*nTAXPERCENT"
>
> This is not an excel formula you so can't substitute XHarbour code into Excel code and visa versa
>
> Formula = "=(("+a1+"+"+a2+")/("+a3+"))*nTAXPERCENT"
>
> I assume nTAXPERCENT is referring to a named Excel range (single cell) which is pre-defined elsewhere in your speadsheet.

thanks again poopall:

'oSheet:Cells( 21,xrow ):Formula = '=if(P' +alltrim(str(xow))+'>0, "unpaid" ,if (P'+alltrim(str(xrow))+'=0, "paid", "cr.bal"))'
--had tried that too...but doesn't work.

' I assume nTAXPERCENT is referring to a named Excel range (single cell) which is pre-defined elsewhere in your speadsheet.'
Formula = "=(("+a1+"+"+a2+")/("+a3+"))*nTAXPERCENT"
--no, it is initially defined as a public variable in main.prg. then, in the do whil !eo()...skip...endd, ntaxpercent value may vary for EACH CELL , on the basis of the 'billyear field' inside the loop after each skip.

in the loop
if field->billyear=2017; ntaxpercent=field->billtaxperc
ntaxpercent=if(ntaxpercent=0,1,ntaxpercent)
e.g. ntaxpercent=15 - so, cell('P'+str(xrow++)) Formula = "=(("+a1+"+"+a2+")/("+a3+"))*nTaxpercent" (i.e. 15)
skip
if field->billyear=2015; ntaxpercent=field->billtaxperc
ntaxpercent is 18 - so, cell('P'+str(xrow++)) Formula = "=(("+a1+"+"+a2+")/("+a3+"))*nTaxpercent" (i.e. 18)
skip
if field->billyear=2021; ntaxpercent=field->billtaxperc
ntaxpercent is nXYZ - so, cell('P'+str(xrow++)) Formula = "=(("+a1+"+"+a2+")/("+a3+"))*nXYZ (i.e...)
skip
....

Re: osheet:cells(X,Y):=value=IF(a=b,c,if(...))

<4acbf692-4c5d-444b-a3da-825675558fd4n@googlegroups.com>

  copy mid

https://www.rocksolidbbs.com/devel/article-flat.php?id=16928&group=comp.lang.xharbour#16928

  copy link   Newsgroups: comp.lang.xharbour
X-Received: by 2002:a05:620a:24c9:: with SMTP id m9mr5817430qkn.615.1642154193520;
Fri, 14 Jan 2022 01:56:33 -0800 (PST)
X-Received: by 2002:a05:620a:80f:: with SMTP id s15mr5789306qks.29.1642154193391;
Fri, 14 Jan 2022 01:56:33 -0800 (PST)
Path: i2pn2.org!i2pn.org!weretis.net!feeder6.news.weretis.net!news.misty.com!border2.nntp.dca1.giganews.com!nntp.giganews.com!news-out.google.com!nntp.google.com!postnews.google.com!google-groups.googlegroups.com!not-for-mail
Newsgroups: comp.lang.xharbour
Date: Fri, 14 Jan 2022 01:56:33 -0800 (PST)
In-Reply-To: <dc326587-b0d4-48d2-be1e-718bdc4a48b2n@googlegroups.com>
Injection-Info: google-groups.googlegroups.com; posting-host=2401:4900:1c20:6ad5:6ce2:9819:7b33:ef0;
posting-account=jZAeNwoAAABYWjMRM-FfqKkHfC3Iph0l
NNTP-Posting-Host: 2401:4900:1c20:6ad5:6ce2:9819:7b33:ef0
References: <bc08c27f-cd5a-47e5-ac5c-1e014c590444n@googlegroups.com>
<578f931d-22ac-41f5-b5c9-47f9214dee5bn@googlegroups.com> <8f2714fb-8999-458e-82ae-0997ec1f484fn@googlegroups.com>
<b7b0c064-ba25-400a-a88a-015a7365367an@googlegroups.com> <51d6682a-6485-4cb8-9731-809f29fd740dn@googlegroups.com>
<04dcf94d-d9e0-4c72-b9fd-5c4b36445cd2n@googlegroups.com> <dc326587-b0d4-48d2-be1e-718bdc4a48b2n@googlegroups.com>
User-Agent: G2/1.0
MIME-Version: 1.0
Message-ID: <4acbf692-4c5d-444b-a3da-825675558fd4n@googlegroups.com>
Subject: Re: osheet:cells(X,Y):=value=IF(a=b,c,if(...))
From: timecosting@gmail.com (timepro timesheet)
Injection-Date: Fri, 14 Jan 2022 09:56:33 +0000
Content-Type: text/plain; charset="UTF-8"
Lines: 38
 by: timepro timesheet - Fri, 14 Jan 2022 09:56 UTC

On Friday, January 14, 2022 at 12:07:18 PM UTC+5:30, poopall wrote:
> On Friday, 14 January 2022 at 4:47:19 pm UTC+11, PARESH VALIA wrote:
> > thanks poopall:
> >
> > 'I think you are trying to learn the basic of Excel and Harbour at the same time '
> > -pretty much...
> >
> > ' oSheet:Cells( 21,xxx ):Formula = "=if({write your excel formula here} )"'
> > -tried oSheet:Cells( 21,xrow ):Formula = "=if({'P'+alltrim(str(xow))>0, 'unpaid',if('P'+alltrim(str(xrow))=0, 'paid', 'cr.bal'))} )"
> > doe not work. (is my coding/syntax wrong)
> >
> > also,
> > how to use a VARIABLE with/in sum() or formula()
> > a1="E"+alltrim(str(xrow))
> > a2="J"+alltrim(str(xrow))
> > a3="N"+alltrim(str(xrow))
> > :Formula = "=((&a1+&a2)/(&a3))*nTAXPERCENT" && processing, it does not pick the value of ntaxpercent (can be same/diff. for each cell - on the basis of the record date)
>
>
> "=if({'P'+alltrim(str(xow))>0, 'unpaid',if('P'+alltrim(str(xrow))=0, 'paid', 'cr.bal'))} )
> Do this
> oSheet:Cells( 21,xrow ):Formula = '=if(P' +alltrim(str(xow))+'>0, "unpaid" ,if (P'+alltrim(str(xrow))+'=0, "paid", "cr.bal"))'
>
> I hav'nt tested so cant guarantee it will work but you need to build the xHarbour string to include what you need for the Excel formulae
>
> Formula = "=((&a1+&a2)/(&a3))*nTAXPERCENT"
>
> This is not an excel formula you so can't substitute XHarbour code into Excel code and visa versa
>
> Formula = "=(("+a1+"+"+a2+")/("+a3+"))*nTAXPERCENT"
>
> I assume nTAXPERCENT is referring to a named Excel range (single cell) which is pre-defined elsewhere in your speadsheet.

thanks, this works

'Do this
oSheet:Cells( 21,xrow ):Formula = '=if(P' +alltrim(str(xow))+'>0, "unpaid" ,if (P'+alltrim(str(xrow))+'=0, "paid", "cr.bal"))'

--oSheet:Cells(xxx,12):Formula='=if(K'+alltrim(str(xxx))+'="CR","Paid",if(K'+alltrim(str(xxx))+'="DE","Defaulted","UnPaid"))'

1
server_pubkey.txt

rocksolid light 0.9.81
clearnet tor