Make A new rule for Custom Formatting

[Mr.WalterJones]

Hi Everyone! Happy monday?

I am trying to format a cell based on two factors. This excel spreadsheet is trying to track deliveries and dates. I would like to highlight a cell only if the date is in the last month and if the another cell is marked "No".

For ex. Column A has a list of dates. Column B is titled "Delivered" and Filled with Yes' and No's. A1 says 3/16/2012 and B1 says No. A1 should be highlighted. A2 also says 3/16/2012 and B2 says Yes. Even though the date is in the past it should not be highlighted.

How do I do this? I was trying to make a rule for it, but I could not figure that one out.

Thank you for your help.

[jeffreybrown]

Hi Walter,

As a first crack, maybe this...

=AND(MONTH(A1)<MONTH(NOW()),B1="No")

[Bob Phillips]

Select ALL cells that are the target of the highlighting, and use conditional formatting with a formula of

=AND(TEXT(A2,"yyyymm")=TEXT(TODAY()-DAY(TODAY()),"yyyymm"),B2="No")

[Mr.WalterJones]

Neither of those inputs did anything. I'm not sure if this is relevant, but the data in both columns are dependent on IF formulas with a false statement of an INDEX formula.

[jeffreybrown]

Since we cannot see what you are seeing, is it possible to give us a workbook sample?

[Mr.WalterJones]

Since we cannot see what you are seeing, is it possible to give us a workbook sample?

I am sorry Jeffrey, but my workbook contains some fairly sensitive accounting information and personal data. My boss would be furious if I did this.

EDIT: These are the exact formulas that make up the cells I am talking about...

G8 contains
=IF('Sheet1'!V2567="","",INDEX('Sheet1'!A2567:AA4197,1,22))
and returns a date such as 2/13/2012

M8 contains
=IF('Sheet1'!Z2567="","",INDEX('Sheet1'!A2567:AA4197,1,26))
and returns either "Yes" or "No" without the quotes.

I am trying to format G8 to be highlighted if M8 contains No and if the date in G8 occurs prior to this month.

[jeffreybrown]

Can you make up a quick mock sample on the side? We are just talking about two cells, A1 and B1. I don't see the If statements causing any interference, but the sample should be as accurate as possible.

[Mr.WalterJones]

Jeff see above edit.

[jeffreybrown]

Hi Walter,

This works for me >> =AND(MONTH(G8)<MONTH(NOW()),M8="No")

Not sure I understand your formula?

If I'm seeing this wrong, doesn't this point to itself?

=IF('Sheet1'!V2567="","",INDEX('Sheet1'!A2567:AA4197,1,22))

[Mr.WalterJones]

The formula written out:

If V2567 from Sheet1 is blank, than leave the cell blank. If not blank index row 1 column 22 from the array A2567:AA4197 from Sheet1. If it is not clear, I am working in two different sheets. However, the problem at hand is only a concern in the second sheet.

Let me try your suggestion again...

[jeffreybrown]

I understand what it is looking for but row 1 column 22 of your array is V2567. Again, maybe not seeing what you are doing, but it seems =IF(V2567="","",V2567) would do the same thing.

You could run Evaluate Formula to see what is happening with your formula.

[Mr.WalterJones]

This works for me >> =AND(MONTH(G8)<MONTH(NOW()),M8="No")

I just want to make sure you are going to conditional formatting ==> make a new rule ==> use a formula to determine which cells to format

[jeffreybrown]

Yes, that is what I am doing.

[Mr.WalterJones]

I understand what it is looking for but row 1 column 22 of your array is V2567. Again, maybe not seeing what you are doing, but it seems =IF(V2567="","",V2567) would do the same thing.

You could run Evaluate Formula to see what is happening with your formula.

Certain cells in column v are blank. I do not want to index those cells if they are. Essentially the formula is saying leave this cell (G8) blank if column V is blank.