• 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

  • Conditional Formatting.xlsx
    9.3 KB · Views: 8
Hi Ravi ,

I am not sure I have understood you. See the attached file.

Narayan
 

Attachments

  • Conditional Formatting.xlsx
    9.4 KB · Views: 6
Hi Ravi,
Good Day...

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

Regards,
 

Attachments

  • Conditional Formatting.xlsx
    9.2 KB · Views: 12
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
 
Thanks Narayan. I wanted it to be done using Visual colours. I will check through the forum archives. thanks again for being of help.

Cheers
 
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 :)
 
Thanks Khalid. in fact i figured out an alternate way to sort this out.

If you look at Column C - that is exactly what i wanted to achieve.

Thanks for your help! - Ravi
 

Attachments

  • Conditional Formatting (1).xlsx
    9.7 KB · Views: 4
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