Results 1 to 2 of 2

Thread: Making sure cells in a column contain same entry

  1. #1
    Acolyte tigerdel's Avatar
    Join Date
    Aug 2012
    Location
    Cambridgeshire
    Posts
    29
    Articles
    0

    Making sure cells in a column contain same entry



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Hi All

    I am collecting data from various sites and need to ensure that the data entered into the cells in column L [Facility] are all identical and if not, highlighted those that are different from L5, warn the user and then open the sheet for them to correct

    Is this possible

    sample sheet attached which would be populated via a userform Sample DCW.xlsb

    Thanks again

    Derek

  2. #2
    Seeker joseph4tw's Avatar
    Join Date
    May 2012
    Location
    South Florida, USA
    Posts
    13
    Articles
    0
    You could use regular cell validation for this if you like.

    Highlight cells L5 to L87. Click Data-->Data Validation-->Custom and then enter this formula:
    Code:
    =IF(AND(L5<>"",L5=$L$5),TRUE,FALSE)
    As long as they start with L5, everything else has to match L5 as they go to L6, L7 and so on.

    The only caveat is if they change L5 again, the rest won't complain until you try to re-enter them again.

    To get those values to show up as red, you could put a conditional format on top of this.

    Highlight L5 to L87 again, click Home-->Conditional Formatting --> New Rule...

    Click "Use a formula to determine..." and in the formula bar, do a slightly different formula:
    Code:
    =IF(AND(L5<>"",L5<>$L$5),TRUE,FALSE)
    Click Format --> Fill --> Red. OK --> OK.


    If that doesn't suit your needs, please let me know and we can go ahead with a VBA solution.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •