Conditional Formatting: based on whether 2 separate cells contain any value

  • Hi all @ Ozgrid,


    I am trying to implement a conditional format which will apply to a range of cells and it's proving trickier than I had thought.


    The objective is as follows;


    I have a workbook with multiple sheets, each with a similar array of data. What I want to achieve is a validation check between the sheets, with the aim being that should a cell in one worksheet have data, and the same corresponding cell in the second worksheet NOT have data then the colour of the cell is changed to highlight this and therefore making it easier for the user to see where gaps appear.


    E.g.


    A1 (sheet1) is BLANK, and A1 (sheet2) is BLANK = No formatting
    A1 (sheet1) is NOT BLANK, and A1 (sheet2) is BLANK = Cell A1 in sheet 2 turns Red
    A1 (sheet1) is BLANK, and A1 (sheet2) is NOT BLANK = Cell A1 in sheet 2 turns Orange



    Problems I have:


    1. Cell A1 in sheet 1 may actually refer to Cell C1 in sheet 2 so I need to incorporate a MATCH function into this somehow (in the attached example, cell F5 on Sheet1 refers to cell E4 on Sheet2)
    2. The second cell always refers to itself and I am not sure how to work around this (have tried using INDIRECT but can't seem to get it working)


    The formula I was using seems not to do the trick.


    I have attached a simple spread sheet as an example.


    If anything in my description is unclear please let me know and I will clarify.


    P.S. The attached example is extracted from another workbook in which I received awesome help with a VBA problem from RoyUK here at ozgrid. The VBA is already in the sheet but is not relevant to this problem, I include it in case someone thinks of using VBA to tackle the problem in case the code clashes.


    However I would prefer it if a "simple" formula were the solution.


    If anyone can offer help with this I would be most appreciative :thanx:

  • Hmmm... No takers so far.


    I've been further trying to resolve this and have made some progress, hopefully someone can help me past the finishing line with this one! I have attached a new sample spreadsheet, which is much simplified.


    In it I have successfully achieved the formatting I want but so far have only managed to make it apply to a single cell. In the attached example this conditional formatting can be found applied to Cell F5 in Sheet2. I have stacked to rules as follows;


    1. =AND(NOT(ISBLANK(Sheet1!$F$5)),COUNTBLANK($F$5)) (Set format 1) - Stop if true
    2. =AND(COUNTBLANK(Sheet1!$F$5),NOT(ISBLANK($F$5))) (Set format 2) - Stop if true


    This now works as needed, in that if there is data in Cell F5 of sheet one, but not in F5 of sheet 2 then then format 1 is set. Vice versa, if there is data in cell F5 of sheet2 but NOT in F5 of sheet1 then format 2 is set. Is the cells in both sheets are blank, then no format is set.


    Why am I looking for blanks instead of matching values you might ask? Simply because the data that is entered into sheet1 will never match that of sheet2, but the sheet user needs to know if the corresponding cells in either sheet contain or do not contain data.


    What I have not been able to do is apply this formula to the entire range of cells in sheet 2. Effectively, the conditional format needs to check the active cell it is referring to and compare it to the corresponding cell in sheet 1 before deciding which conditional format applies. I was thinking that a formula using INDEX & MATCH may be required but I cannot see how to get the conditional formatting to check against dynamic cells (as the formulas I list above use an absolute reference to the cell in question.


    Can anyone please help me with this? I am slowly going bald from all the hair pulling... :angrypc:



    Thanks for looking :thanx:

  • So you are saying that F5 on Sheet 2 doesn't necessarily need to check cell F5 on Sheet1. It needs to check the intersection of A and Jan 1?

    Where there is a will there are many ways. Finding one that works for you is the challenge!

  • Hi NBVC,


    Basically yes. It may not be "A" precisely in the final sheet but yes, it has to return against whatever value is in that cell at the time. However, can Match be made dynamic in that way? I.e. looking at whatever the content of the cell is rather than matching specifically against the test "A"?


    Some further context, the left hand most column (listed A-Z) will be identical on both sheets if that helps.


    Thanks.

  • I am not sure what you mean by "looking at whatever the content of the cell is". What I proposed is to look at the contents of the cell in the other sheet at intersection of A and Jan 1, and see if it is equal to the contents of the active cell of the active sheet.


    If the A-Z in the first column will be the same, will the dates in the first row match up too? If so, then why not use the formula you were using, but don't make it absolute.

    Where there is a will there are many ways. Finding one that works for you is the challenge!

  • Sorry, to clarify further: the column which lists "A-Z" will actually have different values than "A-Z" (a list of names). Your proposal is precisely what I am trying to achieve.


    The dates may not necessarily match between sheets though. The version I uploaded is much simplified which is why I was looking into checking the intersection range of cells.


    Query though, you mentioned not using absolute references in my current formulas, however I can't see a way to prevent this form happening with conditional formatting. Regardless of whether I make the reference absolute ($F$5) or not (F5), and then apply it across multiple cells of the same row to test it, the conditional format formula always refers back to the cell I initially entered as part of the formula. That's one of the reasons I was looking for an Index solution as I can't figure out how to make it dynamic...


    Hope that makes sense.

  • Try these formulas for orange and red, respectively:


    =AND(ISBLANK(INDEX(Sheet1!5:5,MATCH(F$1,Sheet1!$1:$1,0))),NOT(ISBLANK(F5)))


    =AND(NOT(ISBLANK(INDEX(Sheet1!5:5,MATCH(F$1,Sheet1!$1:$1,0)))),COUNTBLANK(F5))


    Review where I have made references absolute vs relative.


    Also change your "Refers to" references to cover entire table. i.e. =$F$5:$AJ$30

    Where there is a will there are many ways. Finding one that works for you is the challenge!

  • Perfect!


    I've just used this on the sample spreadsheet and it works like a charm. I'm going to adapt to the actual spreadsheet now and see if I can make it work.


    Many thanks for the help :)

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!