How to use conditional format on another cell

Closed
bbdwells Posts 3 Registration date Sunday August 10, 2014 Status Member Last seen August 11, 2014 - Aug 10, 2014 at 07:06 AM
 bbdwells - Aug 11, 2014 at 05:24 PM
I want to format a cell using 2 conditions from a another variable cell.

Example
The cell i want to format is A1 using D1.
when D1 is between 0.5 and 0.75 i want AI to be coloured as yellow.
When D1 is between 0.75 and 2 i want A1 to be coloured as red.
If the 2 conditions are not met I want A1 to remain as green.

Please help how can i do this?

5 replies

Mazzaropi Posts 1978 Registration date Monday August 16, 2010 Status Contributor Last seen November 18, 2022 146
Aug 10, 2014 at 08:39 AM
bbdwells, Good morning.

You can use three rules to obtain your differents colored cells.

Select A1.
Menu Conditional Formatting.
Using Formulas.

Rule 1 : =AND(D1>=0.5,D1<=0.75)
.............Choose YELLOW as fill cell.

Rule 2 : =AND(D1>0.75,D1<=2)
.............Choose RED as fill cell.

Rule 3 : =AND(D1<0.5,D1>2)
.............Choose GREEN as fill cell.

IF your cells are coloured as GREEN by default, you don't need to create a 3rd rule.

Is it what you want?
I hope it helps.
--
Belo Horizonte, Brasil.
Marcílio Lobão
0
bbdwells Posts 3 Registration date Sunday August 10, 2014 Status Member Last seen August 11, 2014
Aug 10, 2014 at 02:48 PM
Thank you Marcilio,

the formula seems to work for the cell. I tried to do the same under conditional formatting so that the green, red and yellow icons can be displayed on A1 but it did not work.
Thank you for the formula, it is certainly a first lucky step.

BB
0
Mazzaropi Posts 1978 Registration date Monday August 16, 2010 Status Contributor Last seen November 18, 2022 146
Aug 10, 2014 at 06:15 PM
bbdwells, Good evening.

Please, take a look at this example I did for you:
http://speedy.sh/RTpgH/10-08-2014-enKioskea-Conditional-Format-bbdwells-OK.xlsx

Is it what you want?
I hope it helps.
0
bbdwells Posts 3 Registration date Sunday August 10, 2014 Status Member Last seen August 11, 2014
Aug 11, 2014 at 05:52 AM
Thank you Marcilio,

The rule 1 & 2 works but am having problems with the rule 3. Can you inbox me your email address on bbdwellsatyahoo.com so i can send you the spreadsheet am working with. You will understand better with you see it.
I used the at to represent @
0
Mazzaropi Posts 1978 Registration date Monday August 16, 2010 Status Contributor Last seen November 18, 2022 146
Aug 11, 2014 at 07:56 AM
bbdwells, Good morning.

Thank you for the feedback.

Please, save your file at a free site, www.speedyshare.com and put the link to download it here.

This way all users here can follow the development and solution of your question.
This is a collaborative place.

I waiting for your link.

Have a nice day.
--
Belo Horizonte, Brasil.
Marcílio Lobão
0

Didn't find the answer you are looking for?

Ask a question
Marcilio,

The link to the document is

http://www.speedyshare.com/Fzf62/2014-08-11-NEW-LIVE-PROJECT.xlsx

For example, on the (display page) I3 is Mechanical Engineering the value changes daily. 28 is the number of days to do the task and 14 is the number of days till today. =(E3&"/ "&D3) has been used in {I3} at the mechanical engineering page so that excel does not reduce 14/28 to 1/2.

The value in I3 (display page) comes from K3 on the (mechanical engineering page). (Display page) AF3 is created from M3 on (mechanical Engineering page)

Now, 14/28 is 0.5 as the value changes daily, i want to format the cell I3 (at the display page) to automatically change color, and show icon set based on the value displayed in AF3 (display page).

If possible i will like to configure excel to automatically send emails to notify the project team when the color changes.

I hope you understand my somewhat complex sheet.

Thank you.
0