• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Conditional Formatting

R

Ravi Rajendran

Member
Hello All... Looking for some help with Conditional formatting.

Basically i have two different values for one item. one being the status and another being RAG - which i am looking up from a different table as RED, AMBER & GREEN.

But i am interested in introducing another condition for the RAG based on a Specific Status.
Not Sure if this is possible.

Please see attached spreadsheet. Any help would be really really helpful and appreciated. thanks to you in advance.

Cheers
Ravi R
 

Attachments

Khalid NGO said:
Hi Ravi,
Good Day...

I have used anther CF formula:
=B2="Completed"
See the attached if it is what you were required.

Regards,
Click to expand...

Great Stuff Khalid - i think your idea just might work here as i was trying to introducing an additional conditional formatting over Column C values based status being completed on Column B. This is just what i wanted to achieve.

many thanks for your help!
 
Ravi Rajendran said:
Great Stuff Khalid - i think your idea just might work here as i was trying to introducing an additional conditional formatting over Column C values based status being completed on Column B. This is just what i wanted to achieve.

many thanks for your help!
Click to expand...

Glad it worked... :)

You welcome and thanks for the feedback.
Have a great weekend.
 
Hi Ravi ,

I am still not sure about why you need a formula in the RAG light column , column C.

There are 4 color icon sets available , with RED , GREEN , AMBER and BLACK.

You can use the technique I have followed , and have the 4 color icons in column C ; the formula can be in the lookup table , where instead of mentioning GREEN , use BLACK if the job is completed.

Anyway , you have many options , and you can use which ever one you are comfortable with.

Narayan
 
Hi Narayan - Thanks again! it is needed because the information is being looked up from multiple sheets into the front end which has a consolidated view. so formulas pull in the values from a massive sheet and also apply a extra condition if status was completed. your idea works on 3 colour RAG level and i needed one more. i will still be using ur idea on a different exercise though. all answers are useful as far as i am concerned - its all part of learning! :)

Cheers
Ravi
 
Please if i can one more qq.. Since i am doing a lookup of values and doing a conditional formatting. is it also possible generate values based on Cell colour

Column A Column B Column C
Item # RAG RAG Value
Item 1 GREEN 3
Item 2 RED 1
Item 3 AMBER 2
Item 4 BLACK 4
Item 5 GREY 0
 
Hi Ravi ,

When you say cell colors , are you talking about visual colors or the words themselves ?

If you want that a formula should return 3 if a cell is colored green , and 1 if it colored red , then it is possible using the unsupported Excel 4 Macro functions. If you search , even within this forum , there are quite a few threads on this.

If you are talking about the text , and a formula should return 3 if a cell contains the text Green , then that is much easier.

Narayan
 
Hi again,

Agree with Sir Narayan,

You can use nested if in any column based on lookup values in c:
=IF(C2="BLACK",4,IF(C2="GREEN",3,IF(C2="AMBER",2,IF(C2="RED",1,0))))

If you need the result within current lookup then the current formula will becomes too lengthy:

=IF(VLOOKUP(A2,F:G,2,0)="BLACK",VLOOKUP(A2,F:G,2,0)&" "&4,IF(VLOOKUP(A2,F:G,2,0)="GREEN",VLOOKUP(A2,F:G,2,0)&" "&3,IF(VLOOKUP(A2,F:G,2,0)="AMBER",VLOOKUP(A2,F:G,2,0)&" "&2,IF(VLOOKUP(A2,F:G,2,0)="RED",VLOOKUP(A2,F:G,2,0)&" "&1,VLOOKUP(A2,F:G,2,0)&" "&2))))

Plus you have to modified your CF.

May be someone came with different idea :)
 
Ok Khalid - Basically the RAG Field is by value R 1, A 2 & G 3... i used the actual values to do the CF instead of using the RAG Names - RED, Amber & Green. this is sorted now!
 
So you can use the above formula to get values automatically:

=IF(G2="BLACK",4,IF(G2="GREEN",3,IF(G2="AMBER",2,IF(G2="RED",1,0))))

Similarly you can reverse this if you want Color Names based on values in Column H:
=IF(H2=4,"BLACK",IF(H2=3,"GREEN",IF(H2=2,"AMBER",IF(H2=1,"RED",0))))
 
You must log in or register to reply here.
Back
Top