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