I'm in need of some assistance for a project at work. We're trying to automate a report by conditionally formatting a cell or cells based on information that is updated daily on a separate worksheet in the workbook. Normally this wouldn't be a problem, but I need to match data in 2 columns, then grab the value of a third column in that matching row. Based on the value in that 3rd column, I want to color a cell in another sheet.
So, find a match in Col C, of which there will be many. Then in those rows that qualify, find a specific value in Col D. Once that row is identified (there can be only one row that matches both), go look at Col J and determine if it contains a 1 or 0. If it is a 1, apply a color to the cell on Sheet 2. Each cell in 'Sheet 2'!C3:AA4 (or more) should run this test on Sheet 1 to determine if it's cell should be colored or not.
I've attached a sample so you can see what I'm talking about.
Can anyone help with a formula that will make this happen?
There will be thousands of rows on Sheet 1 and it will be updated (pasted over) daily, so the formula should account for that size.
Thanks in advance!