lundi 27 mai 2019

How to lock the result of the output cell, once it meets a criteria, from subsequent value change in the input cell?

I'm looking to solve this situation:

Depending on the value —variable in time— in B1 (input cell), print a specific "status" in the output cell (D1).

With 3 possible status:

1st: "VALID"

2nd: "PENDING"

3rd: "APPROVED"

with the 3 main properties:

A1 = value fixed (doesn't change in time)

B1 = value changing in time

C1 = string "APPROVED" (to be inputed manually)

D1 = output cell

Following this formula:

=IFS(B1>A1,"VALID",len(C1),"APPROVED",B1<=A1,"PENDING",TRUE,"ERROR")

What I'm trying to do is:

As soon as B1>A1 ("VALID" status) changes to B1<=A1 ("PENDING" status), have the D1 cell status "PENDING" locked to "PENDING" . I.e. not reverting to "VALID" if/when B1<=A1 changes back to B1>A1.

Additionaly, only unlocking the "PENDING" status to the "APPROVED" status if the condition for the Status "APPROVED" (len(C1)-manually inputting the string "APPROVED" into D1) is met subsequently.

Here a simple example:

At T1 (say, May 27 2019, 12:15pm):

B1>A1

A1 = 1.2 (in A1, fixed number)

B1 = 1.5 (in B1, number variable in time)

At T2 (say, May 27 2019, 1:15pm):

B1<=A1

A1 = 1.2 (in (say May 27 2019, 12:15pm))

B1 = 1.2 (or 1.1) (in B1, number variable in time)

At T3 (say, May 27 2019, 2:15pm):

B1>A1

A1 = 1.2 (in A1, fixed number)

B1 = 1.4 (in B1, number variable in time)

At T3 is the issue I'm currently facing.

With this formula:

=IFS(B1>A1,"VALID",len(C1),"APPROVED",B1<=A1,"PENDING",TRUE,"ERROR")

The "PENDING" status (gained from T2) reverts back to "VALID"

How can one lock the "PENDING" status as soon as B1>A1 ("VALID" status) changes to B1<=A1 ("PENDING@ status)?

Then unlock the "PENDING" status when the @APPROVED" string is manually typd into D1?

Thanks a lot for your guidance.

Aucun commentaire:

Enregistrer un commentaire