Apr 09 2022 08:54 PM
I have two sheets together in one notebook. In sheet 1 there are names randomly all around the sheet. Not any particular row or column, but I'm willing to do a formula or condition more than once if needed. In sheet 2, I have a table. In column 2, those same names are all in line. In column 5, each of those names are associated with a number. I want to find a way to highlight all of the names in sheet 1 where the matching name in sheet 2's number is => 95. I know this is really confusing So ill try to make a thing below to visualize it better.
Sheet 1:
Bob Jerry
Kurt
Larry
Sheet 2:
Bob | 100.7
Jerry | 87.4
Larry | 96.3
Kurt | 65.8
I would want Bob and Larry to Highlight on Sheet 1.
Thank you if anyone can figure this out because I have been working so hard on this and cannot figure it out.
Apr 10 2022 12:21 AM
Solution
If the data layout on Sheet2 is as below...
Then select cell A2 on Sheet1 and press Ctrl+Shift+End to select the used range on Sheet1 and make a New Rule for Conditional Formatting using the formula given below and set the format as per your choice.
=AND(A1<>"",INDEX(Sheet2!$E:$E,MATCH(A1,Sheet2!$B:$B,0))>=95)
If the layout on Sheet2 is different, tweak the above formula as required.
Please find the attached with the conditional formatting in place.
Apr 10 2022 12:10 PM
@Subodh_Tiwari_sktneer Thank you so much! You're a life saver
Apr 10 2022 12:46 PM
Apr 10 2022 06:54 PM
You're welcome @nannerb1115!
For the second scenario, you may use the following formula to make a New Rule for Conditional Formatting...
=AND(A1<>"",ISNA(MATCH(A1,Sheet2!$B:$B,0)))
Please find the attached with both the conditional formatting in place.
If that takes care of your original question, please take a minute to accept the post with the proposed solution as a Best Response to mark your question as Solved.
Apr 10 2022 12:21 AM
Solution
If the data layout on Sheet2 is as below...
Then select cell A2 on Sheet1 and press Ctrl+Shift+End to select the used range on Sheet1 and make a New Rule for Conditional Formatting using the formula given below and set the format as per your choice.
=AND(A1<>"",INDEX(Sheet2!$E:$E,MATCH(A1,Sheet2!$B:$B,0))>=95)
If the layout on Sheet2 is different, tweak the above formula as required.
Please find the attached with the conditional formatting in place.