+ Reply to Thread
Results 1 to 5 of 5

More than one Data Validation in a column

  1. #1
    Registered User
    Join Date
    10-23-2019
    Location
    OHIO
    MS-Off Ver
    office 2013
    Posts
    84

    More than one Data Validation in a column

    Hi,

    I have created a rota with the help of the people in this forum that works great but now i want to make it so if i have the same name appear more than once in a single column it will throw up a warning box asking if i want to proceed or not (sometimes it is necessary to have the name more than once).

    This is an example of the Document:

    https://tinyurl.com/ROTAHELP

    I have the following formula:

    =COUNTIF($D:$D,D2)=1

    which works but if i use the =AND or =OR formula to use more than one data validation my original data validation for these cells ceases to work?

    I have tried:

    =AND (Staff,COUNTIF($D:$D,D2)=1)
    =OR (Staff,COUNTIF($D:$D,D2)=1)

    the countif works fine but the macro i have in place which relies on the =staff data validation suddenly stops working and this is essential to block in correctly spelt names.

    Can the geniuses here take a look at my attached example and advise on how i make this work please as i am pulling my hair out!

    Winner gets all the internet cookies!
    Attached Files Attached Files
    Last edited by JON_ROCKS; 07-31-2020 at 06:45 AM.

  2. #2
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,420

    Re: More than one Data Validation in a column

    We prefer the file to be attached here.

    There are instructions at the top of the page explaining how to attach your sample workbook.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    Forum Rules (updated August 2023): please read them here.

  3. #3
    Registered User
    Join Date
    10-23-2019
    Location
    OHIO
    MS-Off Ver
    office 2013
    Posts
    84

    Re: More than one Data Validation in a column

    Hi i did try to follow the instructions but "go advance" doesnt appear for some reason before posting, if you can edit my post to attach the doc it would be appreciated

    *EDIT*
    Never mind, the instructions on the sticky post are outdated, there is no 'Go Advanced' it is under 'Additional Options'.

    Post updated as requested
    Last edited by JON_ROCKS; 07-31-2020 at 06:46 AM.

  4. #4
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,420

    Re: More than one Data Validation in a column

    No, they are not outdated. As I type this reply, the Go Advanced button is clearly visible below the post edit box and to the right, as it is for everyone.

  5. #5
    Registered User
    Join Date
    10-23-2019
    Location
    OHIO
    MS-Off Ver
    office 2013
    Posts
    84

    Re: More than one Data Validation in a column

    Attachment 689224

    Hi,

    I am sure you see it and maybe its dependant on browser but could you point it out in this photo? because on a new post or edit it really does not appear? my intention is not to upset you but the ' go advanced button simply does not appear on mine instead i find the option you mention under 'additional options'

+ 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: 3
    Last Post: 07-22-2019, 06:38 PM
  2. Replies: 1
    Last Post: 01-23-2019, 10:12 AM
  3. Replies: 4
    Last Post: 07-10-2017, 02:49 PM
  4. Replies: 4
    Last Post: 05-18-2017, 04:27 AM
  5. Creating validation list from table column based on data in another column
    By dreamthrum in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 12-11-2013, 08:38 PM
  6. Prevent copying and pasting a cell from a column with data validation to another column
    By kieranoduill in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 08-11-2013, 10:02 AM
  7. Replies: 2
    Last Post: 09-03-2011, 04:31 AM

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