Set up C F to highlight cells where data has been input in error

    I am looking for help to set up Conditional Formatting so that if you forgets to enter data into a Grid 1 cell without first entering it into Grid 2 a cell will be highlighted in red as a warning.


    At the moment an error Warning Label does appear but I would like this extra warning as well.


    I have extracted a test workbook from a much larger app and attached it and hope that I have explained sufficiently. If more info is required please ask and I would be very grateful if anyone can help.


    Many thanks for looking.

    Re: Set up C F to highlight cells where data has been input in error


    you could use a CF to check the two values
    use a formula and add


    =P15=AH21

    ETAF

    Re: Set up C F to highlight cells where data has been input in error


    CF for P11:P33:


    =AND($P11<>"",INDEX($AG$21:$AR$21,MATCH($C10,$AG$9:$AR$9))<>$P11)


    CF for AG21:AR21:


    =INDEX($P$11:$P$33,MATCH(AG$9,$C$10:$C$32))<>AG$21

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

    Re: Set up C F to highlight cells where data has been input in error


    Yes, etaf's would be better solution if the data is always aligned per your sample, but you need to a separate CF for each individual cell, because of your spacing in Grid 1.... I got carried away with formulas, but would need only 1 CF for each set of cells.....

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

    Re: Set up C F to highlight cells where data has been input in error


    Firstly can I thank you both for your incredibly fast response but oh dear got it the wrong way round didn’t I. My original workbook was incorrect and I give you both my apologies and I now attach an amended workbook with the correct info in Red text. I tried a new CF and of course it didn’t work.


    I notice when I was trying out the CF that the first numbered entry in Grid 2 appears in Red but other numbers are in Black which is what I would like even although I made sure the formated should be Black, any thoughts?


    EDIT I should have said that the appropriate cell should turn Red in Col P as well as in Row 23

    Re: Set up C F to highlight cells where data has been input in error


    You mean you want for P11:P33?


    =AND(P11<>"",$AF$27=3,INDEX($AG$21:$AR$21,MATCH($C10,$AG$9:$AR$9))<>$P11)

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

    Re: Set up C F to highlight cells where data has been input in error


    Many thanks, sorry nearly there, I attach the file and as you will see when you put a number in say N19 all the P col cells turn red but I had hoped it would only be P19 that would be red and in Grid 2 it would only be AK19 and this would show the rows and col where the error was, if you get my meaning. Anything you can suggest. Regards

    Re: Set up C F to highlight cells where data has been input in error


    Grid 1 perfect so many thanks, couldn't seem to get Grid 2 to work so put in the original CF formula for Row 21


    INDEX($P$11:$P$33,MATCH(AG$9,$C$10:$C$32))<>AG$21 still no joy' so put $AF$27=3 in front but nothing any last ideas to get Grid 2 to work?

    Re: Set up C F to highlight cells where data has been input in error


    That's it this time and once again many thanks for being so very helpful.

    Re: Set up C F to highlight cells where data has been input in error


    Once again I am looking for help on this problem as I now see that my original request was based on single cells but unfortunately now realise that they are indeed merged cells (I now see the problems associated with them).


    I am looking for an amendment to my original request and wonder if NBVC or anyone else can help and I attach a test file. Many thanks for looking.

    Re: Set up C F to highlight cells where data has been input in error


    It should work. It looks like you may have missed a 3rd parameter in your MATCH() function to find EXACT match.


    Try:


    =AND($P10<>"",INDEX($AG$21:$AR$21,MATCH($C10,$AG$9:$AR$9,0))<>$P10)

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

    Re: Set up C F to highlight cells where data has been input in error


    My goodness what a silly error I made. Once again many thanks, it works perfectly now.

Participate now!

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

Register Yourself Login