how to use if function in Condition formatting cell

[naresh kuvalekar]

Dear Members,

I am a new member in this group. I have a limited knowledge in excel. I would like to learn excel function. Therefore I am attaching an excel file which have data and manually formatted color in cell to match data. I have to put comment based on conditional format of cell. e.g. if cell have green color then put text "match" if cell have red color then put "mismatched" etc. could you please guide which formula to be use in comment.

 

[jayalaxmi]

Dear Members,

I am a new member in this group. I have a limited knowledge in excel. I would like to learn excel function. Therefore I am attaching an excel file which have data and manually formatted color in cell to match data. I have to put comment based on conditional format of cell. e.g. if cell have green color then put text "match" if cell have red color then put "mismatched" etc. could you please guide which formula to be use in comment.

Hi Naresh,

Welcome to the Forum!!

Firstly the result what you had requested is not possible through conditional formatting.

You can apply conditional formatting using values and format it to expected color.

But you can put/show values based on colors.

For eg: if cell have green color then put value matched

The above can be done vice versa i.e if value is matched then you can put cell to have green color. Also one of the particular column/row should contain the data. You can show colors if meeting certain condition such as >1000 etc. something like that.

Please let me know for any further clarifications.

Thank you
Regards
Jaya

 

[naresh kuvalekar]

Thanks Jaya for immediate reply But I am trying to reconcile Invoice no. based on value and in this sheet invoice no is not similar. there is a some small difference hence I highlighted matching value cell manually. It is time consuming job. Is there any method or short cut?
Thank you in advance.
Kind Regards,
Naresh

 

[Peter Bartholomew]

It might be possible to create a formula such as
= IF( ISNUMBER(G19), IF( ISNUMBER(SEARCH(B18,B19)) * (ROUND(G19,0)=ROUND(D18,0)), "matched", "mismatched" ), "" )
that will check to see whether there is a number in column G and, if so, compare the invoice number with that on the previous row and finally check whether the values is column D and G are sufficiently close to be classified as a match.

Really though, the whole of the manual process needs to be reviewed to create a layout in which such rules can be more clearly defined.
The colours should be the result of applying rules rather than user-input.
Either the two data sources should be separate tables or, alternatively, they could be merged to give additional columns but NOT an alternating pattern with manually inserted rows.
The dates should be numerical date-values or (less desirable) strings but not a mixture of the two.

I am sorry to be the bearer of unwelcome news.

 

[naresh kuvalekar]

Hi Peter,

Please accept my apology for late reply. Since I was not in town I am not able to reply you on time.
Thank you very much for your valuable suggestion.
Is there any formula to check invoice number in whole worksheet instead of previous row and match value?. or is it possible to compare to different sheet having same data and return match / mismatch value.

Expecting your valuable suggestion.

Thanking you in advance.

Kind Regards,
Naresh

 

[Peter Bartholomew]

I do not think you are going to enjoy these steps towards a solution
The idea is that the colours should be derived from the numbers, allowing manual processes to be progressively eliminated.

 

[naresh kuvalekar]

I do not think you are going to enjoy these steps towards a solution
The idea is that the colours should be derived from the numbers, allowing manual processes to be progressively eliminated.

Thank You Peter ,

This will reduce my certain work time. But still it is not giving 100% result

Thank you Peter for your reply.

Kind regards,
Naresh D Kuvalekar