+ Reply to Thread
Results 1 to 24 of 24

Data Validation

  1. #1
    Registered User
    Join Date
    03-19-2014
    Location
    United States
    MS-Off Ver
    Excel 365
    Posts
    30

    Data Validation

    Hello I have a worksheet where project details are inserted. The values in Column P are from a drop down list that is stored in a table in another worksheet. i defined that table as a Name_Range.

    what I want to accomplish is the following. if the value in column P selected is "chief" then column O should return the value "yes" otherwise return "no". But if in column P another cell is selected as chief then an error message stating "there can only be on chief in the project".

    How can I do this? please see attached a screen shot of the worksheet.

    Many thanks


    excel picture.png

  2. #2
    Forum Expert kersplash's Avatar
    Join Date
    11-22-2016
    Location
    Perth
    MS-Off Ver
    Home 2016 (Windows 10)/Work 2013 Pro Plus (Windows 10)
    Posts
    2,012

    Re: Data Validation

    You would be better attaching your sample workbook, but you would need a formula in Column O checking Column P value.

    You could conditionally format Column O to highlight if there were more than one Chief = "Yes".

    I think you would need VBA to give you a message box if more than one Chief is selected.

    To attach a workbook;

    Go Advanced -> Manage Attachments -> Upload

  3. #3
    Administrator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    MS365 Family 64-bit
    Posts
    24,531

    Re: Data Validation

    Welcome to the Forum lcasta!

    Use this formula in O2 and copy down (note the the second 2 is deliberately missing a $)

    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Jeff
    | | |會 |會 |會 |會 | |:| | |會 |會
    Read the rules
    Use code tags to [code]enclose your code![/code]

  4. #4
    Administrator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    MS365 Family 64-bit
    Posts
    24,531

    Re: Data Validation

    Note: In my solution the error message is in the cell. If you want a pop-up message box instead, then as kersplash says you need VBA. (You can do that sort of thing with data validation but not when you already using a list; you cannot have two kinds of data validation in the same cell.)

  5. #5
    Forum Expert kersplash's Avatar
    Join Date
    11-22-2016
    Location
    Perth
    MS-Off Ver
    Home 2016 (Windows 10)/Work 2013 Pro Plus (Windows 10)
    Posts
    2,012

    Re: Data Validation

    Could you maybe have a formula validation in Column O that alerts if more than one "Yes"?

  6. #6
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2403
    Posts
    13,396

    Re: Data Validation

    Withdrawn by FR.
    Dave

  7. #7
    Administrator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    MS365 Family 64-bit
    Posts
    24,531

    Re: Data Validation

    Quote Originally Posted by kersplash View Post
    Could you maybe have a formula validation in Column O that alerts if more than one "Yes"?
    I tried that, but the error box doesn't pop up, it just flags the cell with an error. I think that's because the cell has a formula.

  8. #8
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2403
    Posts
    13,396

    Re: Data Validation

    lcasta,

    What if instead of an error message Chief just disappeared from the drop down if Chief appeared in previous rows ... ie. a self diminishing drop down?

    Would that be acceptable?

  9. #9
    Registered User
    Join Date
    03-19-2014
    Location
    United States
    MS-Off Ver
    Excel 365
    Posts
    30

    Re: Data Validation

    Ok here it is the workbook.
    Attached Files Attached Files

  10. #10
    Registered User
    Join Date
    03-19-2014
    Location
    United States
    MS-Off Ver
    Excel 365
    Posts
    30

    Re: Data Validation

    sample of workbook. Thanks
    Attached Files Attached Files

  11. #11
    Registered User
    Join Date
    03-19-2014
    Location
    United States
    MS-Off Ver
    Excel 365
    Posts
    30

    Re: Data Validation

    That would work as well. However Chief is the only that could disappear from the drop down as i will have multiple operation specialists for example.
    How do i do that?
    Thanks

  12. #12
    Registered User
    Join Date
    03-19-2014
    Location
    United States
    MS-Off Ver
    Excel 365
    Posts
    30

    Re: Data Validation

    Jeff, thanks. however it is not acceptable as it inserts "there can only be on chief in the project", instead of stopping input until the error gets resolved.
    This column serves as input and control in a mail merge which drives how the final document reads depending if this person is a chief or not.
    Thank you

  13. #13
    Registered User
    Join Date
    03-19-2014
    Location
    United States
    MS-Off Ver
    Excel 365
    Posts
    30

    Re: Data Validation

    Yes that is my issue as well.

  14. #14
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2403
    Posts
    13,396

    Re: Data Validation

    @ lcasta

    However Chief is the only that could disappear from the drop down as i will have multiple operation specialists for example.
    I take that to mean there are limits to how many of the other StartUp assignments there can be.

    If that is correct please tell us what the rules are for each of the remaining assignments in the DV list.

    BTW: What I have in mind has a "loose end". It would be possible for someone to deliberately copy an existing 'Chief' and paste it into another drop down cell. Would you anticipate such happening? Then as Jeff says you will need some kind of VBA routine to prevent that as well. Unfortunately that is out of my wheelhouse.
    Last edited by FlameRetired; 02-22-2019 at 02:43 PM. Reason: An after thought

  15. #15
    Registered User
    Join Date
    03-19-2014
    Location
    United States
    MS-Off Ver
    Excel 365
    Posts
    30

    Re: Data Validation

    Hello, in the workbook each startup will have its own worksheet. Startup team will be as much as 8 people. Only one person can be the chief for that particular startup, the rest of the positions can be more than one. there are no limits on the number of startups, but i will have only one per worksheet.
    Attached Images Attached Images

  16. #16
    Forum Expert
    Join Date
    07-20-2011
    Location
    Mysore, India.
    MS-Off Ver
    Excel 2019
    Posts
    8,513

    Re: Data Validation

    Column P is already having validation. One more validation cannot done.
    Alternatively worksheet event with VBA code can be used.Is it OK for you.
    Pl note
    Array formula should be confirmed with Ctrl+Shift+Enter keys together.
    If answere is satisfactory press * to add reputation.

  17. #17
    Registered User
    Join Date
    03-19-2014
    Location
    United States
    MS-Off Ver
    Excel 365
    Posts
    30

    Re: Data Validation

    Quote Originally Posted by kersplash View Post
    You would be better attaching your sample workbook, but you would need a formula in Column O checking Column P value.

    You could conditionally format Column O to highlight if there were more than one Chief = "Yes".

    I think you would need VBA to give you a message box if more than one Chief is selected.

    To attach a workbook;

    Go Advanced -> Manage Attachments -> Upload
    .

    here is the workbook. Thanks
    Attached Files Attached Files

  18. #18
    Registered User
    Join Date
    03-19-2014
    Location
    United States
    MS-Off Ver
    Excel 365
    Posts
    30

    Re: Data Validation

    Quote Originally Posted by kvsrinivasamurthy View Post
    Column P is already having validation. One more validation cannot done.
    Alternatively worksheet event with VBA code can be used.Is it OK for you.
    see the validation is there but it is not working. it only flags the cells, but it does not alt the input if an error occurs. I am OK with VBA. However i have no idea how to do it.

    Thanks

  19. #19
    Forum Expert
    Join Date
    07-20-2011
    Location
    Mysore, India.
    MS-Off Ver
    Excel 2019
    Posts
    8,513

    Re: Data Validation

    Code for worksheet event
    Please Login or Register  to view this content.
    To use Code
    On the sheet tab Right click --> View code
    VB window opens
    Insert --> Module
    Copy the code and paste it in VB window
    Close VB window
    Attached Files Attached Files

  20. #20
    Registered User
    Join Date
    03-19-2014
    Location
    United States
    MS-Off Ver
    Excel 365
    Posts
    30

    Re: Data Validation

    Quote Originally Posted by kvsrinivasamurthy View Post
    Code for worksheet event
    Please Login or Register  to view this content.
    To use Code
    On the sheet tab Right click --> View code
    VB window opens
    Insert --> Module
    Copy the code and paste it in VB window
    Close VB window
    Hello kvsrinivasamurthy

    I copied your code and applied it as instructed. Tested the worksheet but no message box displays if i select more than one person as chief.

    How to fix?
    Thanks

  21. #21
    Registered User
    Join Date
    03-19-2014
    Location
    United States
    MS-Off Ver
    Excel 365
    Posts
    30

    Re: Data Validation

    Quote Originally Posted by lcasta View Post
    Hello kvsrinivasamurthy

    I copied your code and applied it as instructed. Tested the worksheet but no message box displays if i select more than one person as chief.

    How to fix?
    Thanks
    @kvsrinivasamurthy

    I got it to work. I copied the code into the VB window without creating a new module. Thanks you

  22. #22
    Registered User
    Join Date
    03-19-2014
    Location
    United States
    MS-Off Ver
    Excel 365
    Posts
    30

    Re: Data Validation

    Thank you all of you for the help rendered to my issue. This has been resolved. Cheers!

  23. #23
    Forum Expert
    Join Date
    07-20-2011
    Location
    Mysore, India.
    MS-Off Ver
    Excel 2019
    Posts
    8,513

    Re: Data Validation

    Thanks for feedback.

  24. #24
    Forum Expert
    Join Date
    07-20-2011
    Location
    Mysore, India.
    MS-Off Ver
    Excel 2019
    Posts
    8,513

    Re: Data Validation

    Sorry for the mistake. Correct procedure is below.
    To use Code
    On the sheet tab Right click --> View code
    VB window opens
    Copy the code and paste it in VB window
    Close VB window

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 1
    Last Post: 01-14-2018, 11:04 AM
  2. Multi-select from data validation isn't working with auto-assigning data validation
    By iPenguin in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 09-11-2017, 12:37 PM
  3. Replies: 3
    Last Post: 06-04-2015, 02:27 PM
  4. [SOLVED] Data Validation: How to clear/delete the content of the cell and not Data Validation List?
    By lukelucky in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 01-07-2015, 09:42 AM
  5. Replies: 4
    Last Post: 12-19-2013, 10:44 AM
  6. Adding Date Data Validation to cells with List Data Validation
    By biggtyme in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 09-03-2013, 09:47 AM
  7. Using Defined Names with Data Validation Depend and Data Validation Multi Select
    By Vinnie Chan in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 03-01-2012, 05:36 PM

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1