Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- 411K Members
- 5,227 Online
- 466K Conversations

- Home
- :
- Excel
- :
- General Discussion
- :
- Conditional Formatting

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Conversation Options

- Subscribe to RSS Feed
- Mark Conversation as New
- Mark Conversation as Read
- Pin this Conversation for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

10-01-2019 10:26 AM

Some of my cells on my spreadsheet need to be the same color as the previous cell but the cell I need to be the same color will always be changing value but it will always be the same color as the previous cell. For instance, any cells that contain the text "Above Average" are orange and the following cell, regardless of the value needs to be the same color as the "Above Average" cell because the two will always be the same value. If it says "Average" and is yellow then the following cell needs to be yellow also. How do I form and apply that rule?

Labels:

4 Replies

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

10-01-2019 10:42 AM

If your data like this

you may apply conditional formatting rule with formula

`=OR(A2="Average",OFFSET(A2,-1,0)="Average")`

to your column starting from A2

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

10-01-2019 12:32 PM

@Sergei Baklan Thank you for your response. So my data looks like the following:

Date | Overall Quadrant | Overall Score | Incentive Quadrant | Incentive Score |

9.27.2019 | Above LCL | 5.07 | Above Average | 7.25 |

So Row 2 Column 2 and Column 3 need to be the same color just as Row 2 Column 4 and Column 5 need to be the same color. The values in Column 3 and Column 5 will always be the same color as Column 2 and Column 4 regardless of the number. So Above LCL is orange and so is 5.07 and Above Average is yellow and so is 7.25. If it were to drop down to below average or any other description then the colors set for those will either be red or green and the following column's value would match the values set for below average and would also be the same color as the color for below average. I guess the simpler way to say this is the Overall Quadrant and Overall Score will always be the same color as will the Incentive Quadrant and the Incentive Score. Sorry, I didn't mean to make that confusing.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

10-01-2019 02:26 PM

Could you please clarify the starting point - why your cell, let say **Above LCL** as in example, is orange or green or red? Do you apply conditional formatting to it use some formula or color manually? In first case you may expand the logic on other cells, if the latest then only with VBA programming.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

10-02-2019 04:14 AM

@Sergei Baklan So this spreadsheet is the daily scorecard for all agents at my job. We have been provided a Mac for employment purposes only and are not allowed to email anything from our work computer to our personal email or anything like that. So I have pulled my row out of the spreadsheet and am manually entering the data daily based on the scorecard and the color key is as follows:

Red is Below LCL, orange is Above LCL, yellow is Above Average, and green is Above LCL. The first four columns of colored cells are labeled as follows: Overall Quadrant and Overall Score which will always be the same color based on what the scores are. If the scores are Below LCL then both cells for that day in those two columns will be red. If the scores go up to Above LCL then those two cells for that day in those two columns will be orange. Then the next two columns of the four columns are labeled as follows: Incentive Quadrant and Incentive Score and the same rules apply for the color code depending on the score. The remaining columns I am able to apply conditional formatting based on the cut-off point for those percentages. I am just recreating an already created spreadsheet but am unable to copy and paste due to the policies in place which doesn't allow the copying or emailing of data. So I just need to extend the conditional formatting for the Overall Score to match the color of the Overall Quadrant, which is determined on a scale of 1-10 with 1 being the lowest and 10 being the highest. Anything that is 1 standard deviation below the average score is Above LCL (orange), anything that is 1 standard deviation above the average score is Above Average (yellow), Anything that is 1 standard deviation above Above Average is Above UCL (green) and anything that is 1 standard deviation below Above LCL is Below LCL (red). The same rule applies to the Incentive Quadrant and the Incentive Score. So I just need to know how to make a rule for the Overall Quadrant which will always produce a result that is text and the Overall Score which will always produce a result that is numeric to make both columns be the same color based on the scores which will show whether it's Below LCL, Above LCL, Above Average, or Above UCL. Hopefully, this answers your question. If not please let me know what other information I can provide. Thank you!

Related Conversations

flashing a white screen while open new tab

cntvertex
in
Discussions
on
10-05-2019
23.2K
Views

10 Likes

13 Replies

Stable version of Edge insider browser

HotCakeX
in
Discussions
on
10-12-2019
24.7K
Views

1 Likes

35 Replies

What is Canary ring in Windows insider program? and how do we get them?

HotCakeX
in
Windows Insider Program
on
09-27-2019
11.1K
Views

0 Likes

9 Replies

How to Prevent Teams from Auto-Launch

chenrylee
in
Microsoft Teams
on
06-27-2019
137K
Views

6 Likes

28 Replies

PacketMon Components are not loading in WAC 1909

HotCakeX
in
Windows Admin Center
on
09-24-2019
1,659
Views

0 Likes

2 Replies

Share

Popular

Learning Resources

Programs

Values

- Contact us
- Privacy & cookies
- Terms of use
- Trademarks
- About our ads
- © Microsoft