ABCDEFGHIJKLMNOPQRSTUVWXYZ
1
1.21.6approvedAPPROVEDHow to lock the result of the output cell, once it meets a criteria, from subsequent value change in the input cell in Google Sheets?
2
3
What I'm trying to do is:
4
5
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.
6
I'm looking to solve this situation:
7
Additionally, only unlocking the "PENDING" status to the "APPROVED" status if/when the condition for the Status "APPROVED" (len(C1)-manually inputting the string "APPROVED" into D1) is met subsequently.Depending on the value —variable in time— in B1 (input cell), print a specific "status" in the output cell (D1).
8
9
With 3 possible status:
10
How can one lock the "PENDING" status as soon as B1>A1 ("VALID" status) changes to B1<=A1 ("PENDING" status)?1st: "VALID"
11
12
Then unlock the "PENDING" status when the "APPROVED" string is manually typed into D1?2nd: "PENDING"
13
14
New question:3rd: "APPROVED"
15
Can we color and bold code the status as well?
16
with the 3 main properties:
17
For example:A1 = value fixed (doesn't change in time)
18
19
If the status is "VALID", then make print the string in green (like this "VALID")B1 = value changing in time
20
21
If the status is "PENDING", then make print the string in Blue (like this "PENDING")C1 = string "APPROVED" (to be inputted manually)
22
23
If the status is "APPROVED", then make print the string in purpule (like this "APPROVED")D1 = output cell
24
25
Following this formula:
26
=IFS(len(C1),"APPROVED",B1>A1,"VALID",B1<=A1,"PENDING",TRUE,"ERROR")
27
APPROVED
28
29
What I'm trying to do is:
30
31
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.
32
33
Additionally, 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.
34
35
Here a simple example of expected result:
36
37
At T1 (say, May 27 2019, 12:15pm):
38
39
B1>A1 -> Status= VALID
40
41
A1 = 1.2 (in A1, fixed number)
42
43
B1 = 1.5 (in B1, number variable in time)
44
45
C1 = (empty cell)
46
47
D1 = VALID
48
49
At T2 (say, May 27 2019, 1:15pm):
50
51
B1<=A1 -> Status= PENDING
52
53
A1 = 1.2 (say May 27 2019, 12:15pm)
54
55
B1 = 1.2 (or 1.1) (in B1, number variable in time)
56
57
C1 = (empty cell)
58
59
D1 = PENDING
60
61
At T3 (say, May 27 2019, 2:15pm):
62
63
B1>A1 -> Status= PENDING (no revert back to VALID despite B1 reverting to B1>A1=
64
65
A1 = 1.2 (in A1, fixed number)
66
67
B1 = 1.4 (in B1, number variable in time)
68
69
C1 = (empty cell)
70
71
D1 = PENDING
72
73
At T4 (say, May 27 2019, 2:15pm):
74
75
A1 = 1.2 (in A1, fixed number)
76
77
B1 = 1.4 or 1.1 (in B1, number variable in time)
78
79
D1 = APPROVED (because of manual input of any character into C1)
80
81
C1 = (any lenght of character into C1)
82
83
(User manually input any character/string into C1 -> Status= APPROVED (because of len(C1), the PENDING Status gets unlocked to APPROVED status)
84
85
At T3 is the issue I'm currently facing.
86
87
With this formula:
88
=IFS(len(C1),"APPROVED",B1>A1,"VALID",B1<=A1,"PENDING",TRUE,"ERROR")
89
APPROVED
90
91
The "PENDING" status (gained from T2) reverts back to "VALID"
92
93
How can one lock the "PENDING" status as soon as B1>A1 ("VALID" status) changes to B1<=A1 ("PENDING" status)?
94
95
Then unlock the "PENDING" status when the "APPROVED" string is manually typed into D1?
96
97
New question: Can we color and bold code the status as well?
98
99
For example:
100