Conditional formatting based on a looked up value.

[smhelgerson72]

I'm working on a dashboard for my workbook, and I'd like to add a quick reference for the status of data on another sheet. The data is simply a Y for Yes or N for No. On the dashboard, I'd like to look up a cell based on a year, month, and account number, then format the cell on my dashboard as red if the cell value is N or green if it is Y.

In the attachment, you'll see I have a drop down for the year and another for the month, as well as a list of account numbers and providers. I'd like to be able to quickly select a month and year, then glance at my account numbers to see if any data is missing. A red cell will tell me I need to check the status of that account for that month.

I found a solution to this problem and thought I would share it in case anyone else would like to use it.
In my table, I changed my Y/N binary for Yes or No to a 1 or 0 for yes or no. Then I used a SUMIFS on my homepage and had it look up the sum of a specific cell. Then I used conditional formatting so if the value was equal to or greater than 1 it gave me the color I wanted. Last, because I used a gradient fill, I changed the font format to ;;; so they showed only the color and no value.

[etaf]

i'm not fully following
The MONTH & YEAR are text format
in the sample - can you give an example of the expected results ?
Which Column has the Y / N

You have a HOME sheet & Invoice tracing sheet

In HOME
you have FY21 & OCT
So what do you want on what sheet
ANY ROW where the MONTH & YEAR match the home
AND Any row where there is a Y OR N ? in what column

[ChemistB]

For No/Red

Please Login or Register  to view this content.

For Yes/Green

Please Login or Register  to view this content.

Is that what you are looking for?

[smhelgerson72]

OK, so in the Invoice Tracking, I'm looking in C and D for the Y or N. I need to tell it to look through columns A, E, and F on the Invoice Tracking tab, and match those with the values in cells C2, C3, and B5:B9, then if the adjacent values in columns C and D on Invoice Tracker are both Y, then highlight the adjacent cell on the Home tab as Green. If one or both of the adjacent cells in column C and D are N, then the cell on the Home tab would be red.
Is that any more clear?

[smhelgerson72]

That's sort of what I'm looking for. It's closer than where I was. I think I could use the second one with conditional formatting and tell it that if the value = False, then show no text and color the cell red? I'll fiddle with that and let you know what I find out.

Thanks!

[bebo021999]

Row 5, sheet home
Count with ACC, Provider, FY and month
Formula A:
=COUNTIFS('Invoice Tracking'!$A$3:$A$69,$B5,'Invoice Tracking'!$B$3:$B$69,$A5,'Invoice Tracking'!$E$3:$E$69,$C$2,'Invoice Tracking'!$F$3:$F$69,$C$3)
gives 4

and add 2 columns with "Y"
Formula B:
=COUNTIFS('Invoice Tracking'!$A$3:$A$69,$B5,'Invoice Tracking'!$B$3:$B$69,$A5,'Invoice Tracking'!$E$3:$E$69,$C$2,'Invoice Tracking'!$F$3:$F$69,$C$3,'Invoice Tracking'!$C$3:$C$69,"Y",'Invoice Tracking'!$D$3:$D$69,"Y")
gives 3

Set defaul color of Home is red, to save one CF formula for red.
then the conditional formula for green:
Formula A=Formula B