Conditional Formatting based on 2 matching criteria

[mattsonberg]

ok, Sooo, I've been searching for a while now and after a lot of trial and error I have not been able to successfully apply the conditional formatting.

I need to have 2 criteria match in order to have the cell colored blue.

'sheet1'!a1 needs to match in column 'sheet2'!f:f
and
'sheet1'!c3 needs to match in column 'sheet2'!a:a

If both values match then I want to color 'sheet1'!c11 blue.

I'm also not sure how this will work when the 'sheet1'!c3 value will have to adjust to c4, c5, c6 etc as the corresponding cells to be highlighted (c11:nc3) continue along..... make sense?

[NeedForExcel]

Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

[mattsonberg]

Sample.xlsx

OK, so there is a "Desired Result" sheet that shows you how I want the "Test Timeline" sheet to look.

I just need to know how to set up the conditional formatting based on the 2 matching criteria that I mentioned above.

I'm having a hard time deciding how to word all of this so please forgive me.

[NeedForExcel]

So basically you want 'sheet1'!c11 blue even If 1 row matches both criteria?

[NeedForExcel]

Give this a Try

On 'sheet1'!c11 use the below formula in conditional formatting.

=SUMPRODUCT(('Daily Report Summary'!$A$2:$A$2000='Test Timeline'!$C$3)*('Daily Report Summary'!$F$2:$F$2000='Test Timeline'!$A$1))>0

[mattsonberg]

I'm not sure I understand your question, but let me try to explain myself a bit better.

I think the confusing part is that I need the Date found in 'sheet1'!3:3 has to match in 'sheet2'a:a. But that's only important if in that same row I find a match for 'sheet1!a1 against 'sheet2'!f:f.

However the date value will change in every cell in my range of cell included for the conditional formatting.

As I was typing this I thought it might help to copy and paste my date values from 3:3 down to 11:11 and simply "hide" them. I thought it might make it easier to do the matching that way.

Does this help at all?

[NeedForExcel]

Did you try the formula in Post #5

[mattsonberg]

Actually I didn't see that til you mentioned it.

I just tried it and I didn't see anything "happen". I didn't get an error though either.

What is that supposed to accomplish?

[NeedForExcel]

It Matches the Date and The Value from the Drop Down in Sheet2, even If 1 row matches both the criteria, the CF will be activated..

[mattsonberg]

OK that makes sense, but does that address the fact that the date value will change across the range of cells to be conditionally formatted?

Example: The date for DS11 (5/1/2013 found above in DS3) will be different than the date for EX11 (6/1/2013 found above in EX3)

[mattsonberg]

I think I got it! I removed the "$"s from the $C$3 after reading about how the CF works and that looks like it did the trick!

I'm cross-checking the results now but at first glance it looks good!

[NeedForExcel]

Great! Let me know if you need further help

[mattsonberg]

I certainly will! Sooo grateful over here!

I'll need to have a phone convo tomorrow morning to see what the client thinks. I may have to add in a 3rd criteria to match against. Would I just add another * followed by the next criterion assuming that we're just trying to return the value greater than "0", which would be a 1 since the value returned on each match is a "1" and if any of them are a "0" then the whole thing would be a "0"?

[NeedForExcel]

Only if all the 3 criteria are true, only then it returns 1..

[mattsonberg]

OK, I've successfully carried out the rest of what I was trying to do by adding the 3rd dimension to the SUMPRODUCT formula. I was hoping to be able to copy/paste all of the blue cells so that I can remove the conditional formatting when I'm all finished. Is there a way to do this? I've tried a couple of different methods and haven't had any luck. I'll keep poking around in the meantime.

[NeedForExcel]

Try this

Press F5 > Special & Select Conditional Formats on this window

111.JPG

This will select all the cells that are Conditionally Formatted.. Then you can go ahead and copy & paste as required..

Hope this is what was required. If not, please clarify further..