Hey folks!



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!

Attached Files

Sample.xls (145.5 KB, 16 views)

Hi Mike,

If you format your Sheet 2 cells as General, instead of Text, you could use:
=SUMPRODUCT(('Sheet 1'!$C$7:$C$15=$A$3)*('Sheet 1'!$B$7:$B$15=$B$3),'Sheet 1'!J$7:J$15)
This will return 0 if there are no matching 1s in column J, or a number greater than 0 otherwise (unless, perhaps, you have -ve numbers in column J).

__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]

Does the attached help?

Attached Files

Copy of sample.xlsx (56.7 KB, 14 views)

__________________
Using O365 v2503 - Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post

Thank you for the quick replies. I haven't had a chance to look at your suggestions yet because it's been very hectic today, but I will try them out and get back with you both.

Thanks again!

macropod,

I was not able to test your formula successfully, mostly because I am not sure where to put it and how to make the cells turn green when the value is >0.

Pecoflyer,

Your modifications to the sample worked flawlessly. However, I could not figure out how to incorporate the changes into the working copy. Due to the sensitive nature of the data, I cannot post the actual file here, so can you walk me through the process to make this work?

Thanks to you both!

OK Mike, first things first - see attached for the named ranges ( CF will not work on different sheets any other way)
So you will have to do the same on your data ( names can be anything just avoid XL terms like sheet, workbook, column,etc..)

Attached Files

Copy of Copy of sample.xlsx (57.6 KB, 14 views)

__________________
Using O365 v2503 - Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post

Ok, I have named my ranges on the first sheet (used the same that you did).

Now what?

Open sheet 2 - Select the range to format ( say C3:AA4)
Select the Home Tab - Cond Formatting - manage rules
Click New rule - Use a formula....
Select " New rule and enter Select which format you want and you're done

If you are not familiar with the SUMPRODUCT function read this link


HTH

__________________
Using O365 v2503 - Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post

Thank you! I had to modify a couple of things, but the formatting is now working perfectly. My biggest mistake was in my named ranges. Since I wanted to allow for future growth of the ranges, I just selected a random range somewhere around cell 5000, but all three ranges were different.

After reading through the link in your previous post I found out that the ranges have to be the same size when used in a calculation. When I changed them all to 5000, the formatting materialized!

Thanks again!

My mistake, I forgot to mention that

__________________
Using O365 v2503 - Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post