Question description:
This user has given permission to use the problem statement for this
blog.
i am trying to make a box change colors based on another boxes value. the issue is I need it to change based on multiple boxes depending on which one is most recent. example, box c4 is white unless box f4 is red, then it turns red as well, however if box g4 is green then c4 is no longer red but instead turns back to white.
Solved by C. Q. in 51 mins
This is the chat thread from the real Excelchat help session. It contains no private user
information.
Excelchat Expert
05/10/2018 - 10:44
Welcome to ExcelChat!
Excelchat Expert
05/10/2018 - 10:44
I see that your question is about conditional formatting. I will help you with the problem and explain the solution afterwards.
Excelchat Expert
05/10/2018 - 10:44
Before we get started, this is a reminder that our policy is one problem per session with additional Q&A on that problem as time allows.
Excelchat Expert
05/10/2018 - 10:44
Let me ask you a couple of quick questions to make sure I fully understand your problem.
Excelchat Expert
05/10/2018 - 10:46
I see that you want to base the conditional formatting based on the fill of another cells
Excelchat Expert
05/10/2018 - 10:46
It is not possible to do, but we can make a nice workaround
Excelchat Expert
05/10/2018 - 10:46
We can make, for example, cell A1 green, if it has text green inside (we can make the text invisible)
Excelchat Expert
05/10/2018 - 10:46
And then we can make cell A2 red, if cell A1 green (it will be based on the invisible text in A1)
Excelchat Expert
05/10/2018 - 10:47
So it will work as you want, the only thing that the manual fill which you do for some of the cells will be done by entering a specific text to those cells
Excelchat Expert
05/10/2018 - 10:47
If you like this solution, please share all the rules that we should apply to it
User
05/10/2018 - 10:49
I attached a file of what im working on, hopefully it helps to explain
[Uploaded an Excel file]
User
05/10/2018 - 10:50
i am attempting to track progressing of training, with the operators name turning red if they aren't up to the expected speed for that week.
Excelchat Expert
05/10/2018 - 10:50
Okay, let's discuss the rules
Excelchat Expert
05/10/2018 - 10:51
I see that for week1 they are the following:
User
05/10/2018 - 10:51
if the following week they achieve the expected speed then i would like that info to supercede the previous data
Excelchat Expert
05/10/2018 - 10:51
As I understand, the cell F2:F6 should be red if their value is 0-10, yellow if 11-20, and green if 21-100, right?
User
05/10/2018 - 10:52
yes correct
Excelchat Expert
05/10/2018 - 10:52
You also have similar rules for week2 and week3
Excelchat Expert
05/10/2018 - 10:52
What about weeks 4-6?
User
05/10/2018 - 10:53
i have the formulas in those boxes as 0-84 = red 85-100=green
Excelchat Expert
05/10/2018 - 10:54
Okay, so if we sum up, here is what you are looking for:
Excelchat Expert
05/10/2018 - 10:54
F2:F6: 0-10 red, 11-20 yellow, 21-100 green
Excelchat Expert
05/10/2018 - 10:54
G2:G6: 0-30 red, 31-40 yellow, 41-100 green
Excelchat Expert
05/10/2018 - 10:54
H2:H6 0-60 red, 61-84 yellow,85-100 green
Excelchat Expert
05/10/2018 - 10:55
I2:K6: 0-84 red, 85-100 green
Excelchat Expert
05/10/2018 - 10:55
Is it all the conditional formattings which I have to make? Or do you have some other rules?
User
05/10/2018 - 10:57
yes, i have all of those formulas applied currently. what i would like is for the name in column C to turn red if one of the boxes in rows F,G,H,I,J,K are red. but turn back to white if the following week is no longer red.
Excelchat Expert
05/10/2018 - 10:58
You have merged cells in column C. Do you mean that you need this cell to be red if any of the cells from range F2:K6 is red?
User
05/10/2018 - 10:58
ex. if f5 is red and no info is present for weeks 2,3,4,5,6 then name = red. however when data is entered into g5 that shows no longer red then i would like the name to no longer be red
Excelchat Expert
05/10/2018 - 10:59
Oh, I got your problem. So if the latest week is not red, then cell is not red as well. If the latest is red, then it is red as well, right?
User
05/10/2018 - 10:59
yes correct
Excelchat Expert
05/10/2018 - 11:00
Great, I will work on the solution for about 10-15 minutes. Please extend the session
User
05/10/2018 - 11:00
how do i extend?
Excelchat Expert
05/10/2018 - 11:00
In general I will just implement all those rules and add some logic..
Excelchat Expert
05/10/2018 - 11:00
You should see the pop up
Excelchat Expert
05/10/2018 - 11:00
Thank you
User
05/10/2018 - 11:00
Thank you
Excelchat Expert
05/10/2018 - 11:03
Will you fill the data for each row together?
Excelchat Expert
05/10/2018 - 11:03
Like when you enter week2, will it be filled for rows 2:6?
User
05/10/2018 - 11:04
yes week twos data for 2-6 will all be manually entered at the same time but will not all be the same value
Excelchat Expert
05/10/2018 - 11:04
Okay, thank you
Excelchat Expert
05/10/2018 - 11:04
Will you fill each week one after another without skipping?
User
05/10/2018 - 11:05
yes there would not be info for week 3 without info for week 2 being entered first
Excelchat Expert
05/10/2018 - 11:11
Sorry, some technical issue
User
05/10/2018 - 11:13
is there anything i should be doing while im waiting?
Excelchat Expert
05/10/2018 - 11:14
I work on the problem, but ExcelChat disconnects me all the time after few seconds of reloading the page. If I won't be able to write you anything till the end of the session, please contact the support by email, they will send you my solution
Excelchat Expert
05/10/2018 - 11:14
Oh, it stopped doing it
Excelchat Expert
05/10/2018 - 11:14
Great
Excelchat Expert
05/10/2018 - 11:15
No, I will write you once I finish
Excelchat Expert
05/10/2018 - 11:19
When you see the pop up, please extend the session again. Sorry, the rule is pretty long.
User
05/10/2018 - 11:19
ok
Excelchat Expert
05/10/2018 - 11:24
Your result is ready
[Uploaded an Excel file]
Excelchat Expert
05/10/2018 - 11:24
Please check it
Excelchat Expert
05/10/2018 - 11:25
Keep in mind the rules which we discussed
Excelchat Expert
05/10/2018 - 11:26
The formula which I used for this rule
Excelchat Expert
05/10/2018 - 11:26
=OR(
AND(MIN(K2:K6)<85,COUNTBLANK(K2:K6)=0),
AND(MIN(J2:J6)<85,COUNTBLANK(J2:J6)=0,COUNTBLANK(K2:K6)=ROWS(K2:K6)),
AND(MIN(I2:I6)<85,COUNTBLANK(I2:I6)=0,COUNTBLANK(J2:K6)=ROWS(K2:K6)*2),
AND(MIN(H2:H6)<61,COUNTBLANK(H2:H6)=0,COUNTBLANK(I2:K6)=ROWS(K2:K6)*3),
AND(MIN(G2:G6)<31,COUNTBLANK(G2:G6)=0,COUNTBLANK(H2:K6)=ROWS(K2:K6)*4),
AND(MIN(F2:F6)<11,COUNTBLANK(F2:F6)=0,COUNTBLANK(G2:K6)=ROWS(K2:K6)*5),
)
Excelchat Expert
05/10/2018 - 11:26
This formula is well formatted at cell D10
User
05/10/2018 - 11:27
it is a protected sheet i cant see if it works properly
Excelchat Expert
05/10/2018 - 11:27
Hm.. it shouldn't be protected
Excelchat Expert
05/10/2018 - 11:28
Please try to download it again
Excelchat Expert
05/10/2018 - 11:28
[Uploaded an Excel file]
User
05/10/2018 - 11:28
it works perfectly thank you so much
Excelchat Expert
05/10/2018 - 11:28
Great to hear that!
User
05/10/2018 - 11:29
my company computer would not allow me to access it at first
Excelchat Expert
05/10/2018 - 11:29
Do you have questions about the solution?
Excelchat Expert
05/10/2018 - 11:29
Please study this formula at cell D10, I tried to format it in understandable way
Excelchat Expert
05/10/2018 - 11:29
Basically we check each week step by step starting from the end
Excelchat Expert
05/10/2018 - 11:29
First we check that it is not blank and that the minimum value fits the criteria
Excelchat Expert
05/10/2018 - 11:30
If something breaks the set limit, it means that formula will return True and cell will become red
Excelchat Expert
05/10/2018 - 11:30
Then we go to the next week
Excelchat Expert
05/10/2018 - 11:30
We check that week6 is empty, and week5 is not empty, and that it follows the rules
Excelchat Expert
05/10/2018 - 11:30
And so on we move to the final column
Excelchat Expert
05/10/2018 - 11:31
In final column we check if weeks 2:6 are empty, if week1 is note empty, and if it follows the set limit
Excelchat Expert
05/10/2018 - 11:31
The logic is the same, we just produce it step by step
Excelchat Expert
05/10/2018 - 11:33
If you don't have more questions, I believe we may finish. It was a pleasure to assist you! If it happens that you have any Excel or Google Sheets problems in future, please don't hesitate to reach us again.
Excelchat Expert
05/10/2018 - 11:33
You may now end the session, I would highly appreciate if you rate our session at the end and leave some feedback. Have a nice day!
User
05/10/2018 - 11:35
it will take me a while to understand this formula, it is far more complex than anything i have worked on before i appreciate your help and will gladly give feedback. good day
Excelchat Expert
05/10/2018 - 11:35
Thank you!
This is the output file from the real Excelchat help session:
This is an example of the expert help you can get. It contains no private user
information.
