software

In a monthly report I have a requirement to track a certain type of day (
AE10 ) that is dependant upon two other day group conditions ( L10:P10, and
AB10 ). The answer cell ( AE10 ) should indicate either a 0 or 1.

gt;L10:P10 ( ie. =SUM 3.8 hrs )
gt;AB10 ( Control Check box, Linked to AI10 )
gt;AH10 ( =IFAI10.1.0 )
gt;AE10 ( Should be 0 or 1, depending upon L10:P10, and AB10 )

If L10:P10 has accumulated hours, and AB10 is Contrtol Box Checked (1),
then AE10 =1.
If L1010 has no accumulated hours, and AB10 is Control Box Checked (1),
then AE10=0.
If L10:P10 has no accumulated hours, and AB10 is not Control Box Checked
(0), then AE10=0.
If L10:P10 has no accumulated hours, and AB10 is Not Control Box Checked
(0), then AE10=0.

Help is appreciated! Thanks
Assuming you're using the Check box from the Forms Toolbar, here's some
options to play with:

As it stands, with your set-up as described,

Put in AE10, either:
=IF(AND(SUM(L10:P10)lt;gt;0,AH10=1),1,0)
or
=IF((SUM(L10:P10)lt;gt;0)*(AH10=1),1,0)

But I think we can dispense with the formula in AH10,
and read the checkbox's link cell AI10 directly

In which case, we could try instead in AE10, either:
=IF(AND(SUM(L10:P10)lt;gt;0,AI10),1,0)
or
=IF((SUM(L10:P10)lt;gt;0)*AI10,1,0)

(the formula in AH10 can be deleted)
--
Max
Singapore
savefile.com/projects/236895
xdemechanik
---
quot;Kanequot; gt; wrote in message
...
gt;
gt; In a monthly report I have a requirement to track a certain type of day (
gt; AE10 ) that is dependant upon two other day group conditions ( L10:P10,
and
gt; AB10 ). The answer cell ( AE10 ) should indicate either a 0 or 1.
gt;
gt; gt;L10:P10 ( ie. =SUM 3.8 hrs )
gt; gt;AB10 ( Control Check box, Linked to AI10 )
gt; gt;AH10 ( =IFAI10.1.0 )
gt; gt;AE10 ( Should be 0 or 1, depending upon L10:P10, and AB10 )
gt;
gt; If L10:P10 has accumulated hours, and AB10 is Contrtol Box Checked (1),
gt; then AE10 =1.
gt; If L1010 has no accumulated hours, and AB10 is Control Box Checked (1),
gt; then AE10=0.
gt; If L10:P10 has no accumulated hours, and AB10 is not Control Box Checked
gt; (0), then AE10=0.
gt; If L10:P10 has no accumulated hours, and AB10 is Not Control Box Checked
gt; (0), then AE10=0.
gt;
gt; Help is appreciated! Thanks
Thanks ... Your info was most helpful. I had to change it slightly as I had
given all of the correct parameters. It works!

.... Kane

quot;Maxquot; wrote:

gt; Assuming you're using the Check box from the Forms Toolbar, here's some
gt; options to play with:
gt;
gt; As it stands, with your set-up as described,
gt;
gt; Put in AE10, either:
gt; =IF(AND(SUM(L10:P10)lt;gt;0,AH10=1),1,0)
gt; or
gt; =IF((SUM(L10:P10)lt;gt;0)*(AH10=1),1,0)
gt;
gt; But I think we can dispense with the formula in AH10,
gt; and read the checkbox's link cell AI10 directly
gt;
gt; In which case, we could try instead in AE10, either:
gt; =IF(AND(SUM(L10:P10)lt;gt;0,AI10),1,0)
gt; or
gt; =IF((SUM(L10:P10)lt;gt;0)*AI10,1,0)
gt;
gt; (the formula in AH10 can be deleted)
gt; --
gt; Max
gt; Singapore
gt; savefile.com/projects/236895
gt; xdemechanik
gt; ---
gt; quot;Kanequot; gt; wrote in message
gt; ...
gt; gt;
gt; gt; In a monthly report I have a requirement to track a certain type of day (
gt; gt; AE10 ) that is dependant upon two other day group conditions ( L10:P10,
gt; and
gt; gt; AB10 ). The answer cell ( AE10 ) should indicate either a 0 or 1.
gt; gt;
gt; gt; gt;L10:P10 ( ie. =SUM 3.8 hrs )
gt; gt; gt;AB10 ( Control Check box, Linked to AI10 )
gt; gt; gt;AH10 ( =IFAI10.1.0 )
gt; gt; gt;AE10 ( Should be 0 or 1, depending upon L10:P10, and AB10 )
gt; gt;
gt; gt; If L10:P10 has accumulated hours, and AB10 is Contrtol Box Checked (1),
gt; gt; then AE10 =1.
gt; gt; If L1010 has no accumulated hours, and AB10 is Control Box Checked (1),
gt; gt; then AE10=0.
gt; gt; If L10:P10 has no accumulated hours, and AB10 is not Control Box Checked
gt; gt; (0), then AE10=0.
gt; gt; If L10:P10 has no accumulated hours, and AB10 is Not Control Box Checked
gt; gt; (0), then AE10=0.
gt; gt;
gt; gt; Help is appreciated! Thanks
gt;
gt;
gt;

Glad it worked, Kane !
You're welcome, and thanks for the feedback ..
--
Max
Singapore
savefile.com/projects/236895
xdemechanik
---
quot;Kanequot; gt; wrote in message
...
gt; Thanks ... Your info was most helpful. I had to change it slightly as I
had
gt; given all of the correct parameters. It works!
gt;
gt; ... Kane
MAx.. Not sure if I can use this route again, but here goes!
I now have a real problem that expands upon this scenario. I changed the
formula to read lt; =If(AND(SUM(L10:P10)=0,AI10),1,0) gt; , as I was incorrect in
stating condition 2. Condition 2 should have been lt; L10:P10 has no
accumulated hours( 0.0), and AB10 is checked (1), then AE10=1gt;.

To start over might be better. We want to record chargeable quot;Standbyquot; time
dependant upon the# of truck types ( dependant upon weight ) driven ( max of
three types ). A selection is made ( one of 7 combinations ) using Control
Box,quot;CheckBoxquot; (1 or 0) and supporting cell (format control, checkbox)
indicating the time ( 0.0 hours).
If there is no actual time (hours) entered , amp; or hours driven distribution,
then Standard quot;StandByquot; hours( 1=7.8 , 2=3.9 each, 3=2.6 hours) are entered
in the max of three designated truck type columns..
I changed your formula tolt; =IF(AND(SUM(D10:K10:L10:P10)=0,Al10),1,0) gt; and
it worked fine. Then, the rules changed and here are the real scenario's!
**** 4 Conditions
1.gt; If Start and Stop hours are entered in B10 amp; C10, with results in I10;
If lt; D10:K10:L10:P10 gt; HAS actual hours accumulated (5.0); and lt;
AB10 gt; is Control box quot;Checkedquot; (1), then lt; AQ10=1 gt;
2.gt; If lt; D10:K10:L10:P10 gt; has NO accumulated hours (0.0) entered; and lt;
AB10 gt; is control box checked (1); then lt; AQ10=1 gt;
3.gt;If D10:K10:L10:P10 gt; has accumulated hours (5.0), and lt; AB10 gt; is NOT
checked (0), then lt; AQ10=0 gt;.
4,gt; If lt; D10:K10:L10:P10 gt;has NO accumulated hours (0.), and lt; AB10 gt; is NOT
checked; then lt; AQ10=0 gt;

The time distribution determined by the number of truck types is based on
7.8 hours per day,and is selectable using a control check box.

If 1, then AM54,AM55, AM56 =7.8 hours
If 2, then AP54, AP55, AP56=7.8/2=3.9 hours
If 3, then AS54=7.8/3=2.6 hours

I hope that this makes some kind of sence!?

Should I re-post this ?quot;Maxquot; wrote:

gt; Glad it worked, Kane !
gt; You're welcome, and thanks for the feedback ..
gt; --
gt; Max
gt; Singapore
gt; savefile.com/projects/236895
gt; xdemechanik
gt; ---
gt; quot;Kanequot; gt; wrote in message
gt; ...
gt; gt; Thanks ... Your info was most helpful. I had to change it slightly as I
gt; had
gt; gt; given all of the correct parameters. It works!
gt; gt;
gt; gt; ... Kane
gt;
gt;
gt;

Admit it's a bit confusing, Kane lt;ggt;. Anyway, here's one deep guess at
interpreting your set-up and what you're after

Put in AQ10:
=IF(AND(SUM(D10,K10,L10:P10)gt;=5,NOT(AI10)),0,
IF(AND(SUM(D10,K10,L10:P10)=0,NOT(AI10)),0,1))

Test out the output in AQ10 with all the scenarios
(Lightly tested here, it seems to function as it should)

Some clarifications:
I've presumed the checkbox's link cell is still AI10, and that you want to
monitor cells: D10, K10, and L10:P10. Following through from your orig.
post, I've read your description of: quot;L10:P10quot; to mean *all* the cells
between L10 to P10, inclusive. If this is not the case and you actually
mean: L10, P10 only, then just change the part:

SUM(D10,K10,L10:P10)

to

SUM(D10,K10,L10,P10)

in AQ10's formula

And I've also disregarded your line:

gt; 1.gt; If Start and Stop hours are entered in B10 amp; C10, with results in I10;

as cells B10, C10 and I10 don't seem to figure in the scenario details which
follow thereafter ..
--
Max
Singapore
savefile.com/projects/236895
xdemechanik
---
quot;Kanequot; gt; wrote in message
...
gt; MAx.. Not sure if I can use this route again, but here goes!
gt; I now have a real problem that expands upon this scenario. I changed the
gt; formula to read lt; =If(AND(SUM(L10:P10)=0,AI10),1,0) gt; , as I was incorrect
in
gt; stating condition 2. Condition 2 should have been lt; L10:P10 has no
gt; accumulated hours( 0.0), and AB10 is checked (1), then AE10=1gt;.
gt;
gt; To start over might be better. We want to record chargeable quot;Standbyquot; time
gt; dependant upon the# of truck types ( dependant upon weight ) driven ( max
of
gt; three types ). A selection is made ( one of 7 combinations ) using Control
gt; Box,quot;CheckBoxquot; (1 or 0) and supporting cell (format control, checkbox)
gt; indicating the time ( 0.0 hours).
gt; If there is no actual time (hours) entered , amp; or hours driven
distribution,
gt; then Standard quot;StandByquot; hours( 1=7.8 , 2=3.9 each, 3=2.6 hours) are
entered
gt; in the max of three designated truck type columns..
gt; I changed your formula tolt; =IF(AND(SUM(D10:K10:L10:P10)=0,Al10),1,0) gt; and
gt; it worked fine. Then, the rules changed and here are the real scenario's!
gt; **** 4 Conditions
gt; 1.gt; If Start and Stop hours are entered in B10 amp; C10, with results in I10;
gt; If lt; D10:K10:L10:P10 gt; HAS actual hours accumulated (5.0); and
lt;
gt; AB10 gt; is Control box quot;Checkedquot; (1), then lt; AQ10=1 gt;
gt; 2.gt; If lt; D10:K10:L10:P10 gt; has NO accumulated hours (0.0) entered; and lt;
gt; AB10 gt; is control box checked (1); then lt; AQ10=1 gt;
gt; 3.gt;If D10:K10:L10:P10 gt; has accumulated hours (5.0), and lt; AB10 gt; is NOT
gt; checked (0), then lt; AQ10=0 gt;.
gt; 4,gt; If lt; D10:K10:L10:P10 gt;has NO accumulated hours (0.), and lt; AB10 gt; is
NOT
gt; checked; then lt; AQ10=0 gt;
gt;
gt; The time distribution determined by the number of truck types is based on
gt; 7.8 hours per day,and is selectable using a control check box.
gt;
gt; If 1, then AM54,AM55, AM56 =7.8 hours
gt; If 2, then AP54, AP55, AP56=7.8/2=3.9 hours
gt; If 3, then AS54=7.8/3=2.6 hours
gt;
gt; I hope that this makes some kind of sence!?
gt;
gt; Should I re-post this ?
Max... I have not tried this yet,but a question re. Condition1. The time I
indicated (5.0) could be any number of hours amp; minutes, depending upon the
input start (B10) and stop (C10) time. How would this affect the below AQ10
formula?

....Kane

quot;Maxquot; wrote:

gt; Admit it's a bit confusing, Kane lt;ggt;. Anyway, here's one deep guess at
gt; interpreting your set-up and what you're after
gt;
gt; Put in AQ10:
gt; =IF(AND(SUM(D10,K10,L10:P10)gt;=5,NOT(AI10)),0,
gt; IF(AND(SUM(D10,K10,L10:P10)=0,NOT(AI10)),0,1))
gt;
gt; Test out the output in AQ10 with all the scenarios
gt; (Lightly tested here, it seems to function as it should)
gt;
gt; Some clarifications:
gt; I've presumed the checkbox's link cell is still AI10, and that you want to
gt; monitor cells: D10, K10, and L10:P10. Following through from your orig.
gt; post, I've read your description of: quot;L10:P10quot; to mean *all* the cells
gt; between L10 to P10, inclusive. If this is not the case and you actually
gt; mean: L10, P10 only, then just change the part:
gt;
gt; SUM(D10,K10,L10:P10)
gt;
gt; to
gt;
gt; SUM(D10,K10,L10,P10)
gt;
gt; in AQ10's formula
gt;
gt; And I've also disregarded your line:
gt;
gt; gt; 1.gt; If Start and Stop hours are entered in B10 amp; C10, with results in I10;
gt;
gt; as cells B10, C10 and I10 don't seem to figure in the scenario details which
gt; follow thereafter ..
gt; --
gt; Max
gt; Singapore
gt; savefile.com/projects/236895
gt; xdemechanik
gt; ---
gt; quot;Kanequot; gt; wrote in message
gt; ...
gt; gt; MAx.. Not sure if I can use this route again, but here goes!
gt; gt; I now have a real problem that expands upon this scenario. I changed the
gt; gt; formula to read lt; =If(AND(SUM(L10:P10)=0,AI10),1,0) gt; , as I was incorrect
gt; in
gt; gt; stating condition 2. Condition 2 should have been lt; L10:P10 has no
gt; gt; accumulated hours( 0.0), and AB10 is checked (1), then AE10=1gt;.
gt; gt;
gt; gt; To start over might be better. We want to record chargeable quot;Standbyquot; time
gt; gt; dependant upon the# of truck types ( dependant upon weight ) driven ( max
gt; of
gt; gt; three types ). A selection is made ( one of 7 combinations ) using Control
gt; gt; Box,quot;CheckBoxquot; (1 or 0) and supporting cell (format control, checkbox)
gt; gt; indicating the time ( 0.0 hours).
gt; gt; If there is no actual time (hours) entered , amp; or hours driven
gt; distribution,
gt; gt; then Standard quot;StandByquot; hours( 1=7.8 , 2=3.9 each, 3=2.6 hours) are
gt; entered
gt; gt; in the max of three designated truck type columns..
gt; gt; I changed your formula tolt; =IF(AND(SUM(D10:K10:L10:P10)=0,Al10),1,0) gt; and
gt; gt; it worked fine. Then, the rules changed and here are the real scenario's!
gt; gt; **** 4 Conditions
gt; gt; 1.gt; If Start and Stop hours are entered in B10 amp; C10, with results in I10;
gt; gt; If lt; D10:K10:L10:P10 gt; HAS actual hours accumulated (5.0); and
gt; lt;
gt; gt; AB10 gt; is Control box quot;Checkedquot; (1), then lt; AQ10=1 gt;
gt; gt; 2.gt; If lt; D10:K10:L10:P10 gt; has NO accumulated hours (0.0) entered; and lt;
gt; gt; AB10 gt; is control box checked (1); then lt; AQ10=1 gt;
gt; gt; 3.gt;If D10:K10:L10:P10 gt; has accumulated hours (5.0), and lt; AB10 gt; is NOT
gt; gt; checked (0), then lt; AQ10=0 gt;.
gt; gt; 4,gt; If lt; D10:K10:L10:P10 gt;has NO accumulated hours (0.), and lt; AB10 gt; is
gt; NOT
gt; gt; checked; then lt; AQ10=0 gt;
gt; gt;
gt; gt; The time distribution determined by the number of truck types is based on
gt; gt; 7.8 hours per day,and is selectable using a control check box.
gt; gt;
gt; gt; If 1, then AM54,AM55, AM56 =7.8 hours
gt; gt; If 2, then AP54, AP55, AP56=7.8/2=3.9 hours
gt; gt; If 3, then AS54=7.8/3=2.6 hours
gt; gt;
gt; gt; I hope that this makes some kind of sence!?
gt; gt;
gt; gt; Should I re-post this ?
gt;
gt;
gt;

gt; How would this affect the below AQ10 formula?

Yes, think there'll be impact on the criteria (eg: is time entered as time
format: 3:30, or as a number: 3.5, etc.)

Think it's much easier to take a look at what you really have over there.
Could you upload a quot;sanitizedquot; small sample copy of your file via a free
filehost* and then post the *link* to it in response here (the link will be
generated when you quot;uploadquot; in the filehost (follow the instructions there),
then just copy the link and paste it into your reply to this post)

*Some free filehosts that could be used:
www.flypicture.com/
cjoint.com/index.php
www.savefile.com/index.php

For cjoint.com (it's in French), just click quot;Browsequot; button,
navigate to your folder gt; select the sample file gt; Open, then click the
button centred in the page below (labelled quot;Creer le lien Cjointquot;) and it'll
generate the link. Then copy amp; paste the generated link as part and parcel
of your response here.

Kindly note that no attachments
should be posted *directly* to the newsgroup

Posting a link to your sample here (instead of going off-line) will enable
the discussions to be continued within the newsgroup, visible to and for the
benefit of all readers. And there could well be better insights coming from
others, too lt;ggt;.
--
Max
Singapore
savefile.com/projects/236895
xdemechanik
---
quot;Kanequot; gt; wrote in message
...
gt; Max... I have not tried this yet,but a question re. Condition1. The time I
gt; indicated (5.0) could be any number of hours amp; minutes, depending upon the
gt; input start (B10) and stop (C10) time. How would this affect the below
AQ10
gt; formula?
gt;
gt; ...Kane
Max... I would like to follow up on this within the next week. I have been
out of town and not had time to continue. ....

Kane

quot;Maxquot; wrote:

gt; gt; How would this affect the below AQ10 formula?
gt;
gt; Yes, think there'll be impact on the criteria (eg: is time entered as time
gt; format: 3:30, or as a number: 3.5, etc.)
gt;
gt; Think it's much easier to take a look at what you really have over there.
gt; Could you upload a quot;sanitizedquot; small sample copy of your file via a free
gt; filehost* and then post the *link* to it in response here (the link will be
gt; generated when you quot;uploadquot; in the filehost (follow the instructions there),
gt; then just copy the link and paste it into your reply to this post)
gt;
gt; *Some free filehosts that could be used:
gt; www.flypicture.com/
gt; cjoint.com/index.php
gt; www.savefile.com/index.php
gt;
gt; For cjoint.com (it's in French), just click quot;Browsequot; button,
gt; navigate to your folder gt; select the sample file gt; Open, then click the
gt; button centred in the page below (labelled quot;Creer le lien Cjointquot;) and it'll
gt; generate the link. Then copy amp; paste the generated link as part and parcel
gt; of your response here.
gt;
gt; Kindly note that no attachments
gt; should be posted *directly* to the newsgroup
gt;
gt; Posting a link to your sample here (instead of going off-line) will enable
gt; the discussions to be continued within the newsgroup, visible to and for the
gt; benefit of all readers. And there could well be better insights coming from
gt; others, too lt;ggt;.
gt; --
gt; Max
gt; Singapore
gt; savefile.com/projects/236895
gt; xdemechanik
gt; ---
gt; quot;Kanequot; gt; wrote in message
gt; ...
gt; gt; Max... I have not tried this yet,but a question re. Condition1. The time I
gt; gt; indicated (5.0) could be any number of hours amp; minutes, depending upon the
gt; gt; input start (B10) and stop (C10) time. How would this affect the below
gt; AQ10
gt; gt; formula?
gt; gt;
gt; gt; ...Kane
gt;
gt;
gt;

No prob. Just upload your sample and post the link. Then we can take a look
at it.
--
Max
Singapore
savefile.com/projects/236895
xdemechanik
---
quot;Kanequot; gt; wrote in message
...
gt; Max... I would like to follow up on this within the next week. I have been
gt; out of town and not had time to continue. ....
gt;
gt; Kane