+ Reply to Thread
Results 1 to 12 of 12

Google Sheets: Problem with Data Validation Formula

  1. #1
    Registered User
    Join Date
    01-08-2022
    Location
    North Carolina, USA
    MS-Off Ver
    Office
    Posts
    6

    Google Sheets: Problem with Data Validation Formula

    Hi!
    I am working on a spreadsheet for preschool registration.

    In the 1's classroom, we can have a maximum of 9 children on any given day. Children are offered either 2 days/week, 3 days/week, or 5 days/week.

    What I would like to do is format the 2 days/week and 3 days/week columns so that you cannot enter a "X" if you have more than 2 of either 2 day or 3 day without the corresponding 2 day or 3 day slot filled. (I do not want to have all 2 day children and no 3 day children. I only want to allow for 2 slots without the other days filled.)

    In addition to this, if possible, I would like to have my total working so that we do not exceed 9 children on any given set of days.

    I have the formula =IF(OR(B2=C2,B2<>C2+1,B2=C2-1)) in data validation and in Conditional Formats I have the cells green if they are not empty. I also have a total at the bottom of B and C that uses the rule =COUNTIF(B2:B15,"X") to format the total X's.

    My totals are 0 right now. I am getting a rule violation if I try to add any X's in B or C. What am I doing wrong?
    Thanks for any help! I really don't know what I'm doing and have spend hours and hours just getting to here!
    Paige
    Last edited by AliGW; 01-09-2022 at 06:05 PM.

  2. #2
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel (Windows 11 64-bit)
    Posts
    60,630

    Re: Problem with Data Validation Formula

    Welcome to the forum.

    Is this for Excel or Google Sheets?

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

    A good sample workbook has just 10-20 rows of representative data that has been desensitised. It also has expected results mocked up, relevant cells highlighted and a few explanatory notes.

    Administrative Note:

    Members will tailor the solutions they offer to the version of Office (Excel) that you have. Please check that your forum profile is up-to-date in this respect. If you aren't sure, in Excel go to File | Account and report what it says below the MS logo at the top of that page. If your version is for Mac, please also state this. Thanks.
    Ali


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


    Forum Rules (updated September 2018): please read them here.
    How to use the Power Query code you've been given: help here. More about the Power suite here.

  3. #3
    Registered User
    Join Date
    01-08-2022
    Location
    North Carolina, USA
    MS-Off Ver
    Office
    Posts
    6

    Re: Problem with Data Validation Formula

    Thank you. I have read the instructions for how to upload it, but cannot figure it out. Thank you for your help anyway.

  4. #4
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel (Windows 11 64-bit)
    Posts
    60,630

    Re: Problem with Data Validation Formula

    Is this for Excel or Google Sheets?

  5. #5
    Registered User
    Join Date
    01-08-2022
    Location
    North Carolina, USA
    MS-Off Ver
    Office
    Posts
    6

    Re: Problem with Data Validation Formula

    Google Sheet

  6. #6
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel (Windows 11 64-bit)
    Posts
    60,630

    Re: Google Sheets: Problem with Data Validation Formula

    OK, thanks. What is the issue with uploading? What part of the instructions were you not able to follow?

  7. #7
    Registered User
    Join Date
    01-08-2022
    Location
    North Carolina, USA
    MS-Off Ver
    Office
    Posts
    6

    Re: Google Sheets: Problem with Data Validation Formula

    I'm not sure where to go to find the file to upload. I tried saving the "address" and copying it but that did not go thru. And when I try saving the file, it says that it is not a valid file.

  8. #8
    Valued Forum Contributor
    Join Date
    07-24-2021
    Location
    Japan
    MS-Off Ver
    Google Sheets (& Mac 2021)
    Posts
    797

    Re: Google Sheets: Problem with Data Validation Formula

    If you want to share a link to a google sheets file put the link (editor for anyone with the link) into a text file.

    In the bottom right of the reply window, click go advanced

    Then select the file from computer, then click the tiny upload

    After it is done then in the top right click close

    It will bring you back to complete your reply.

  9. #9
    Registered User
    Join Date
    01-08-2022
    Location
    North Carolina, USA
    MS-Off Ver
    Office
    Posts
    6

    Re: Google Sheets: Problem with Data Validation Formula

    Maybe it is there now? I may have just given you access to all of my dropbox files?
    Thank you for your help.
    Last edited by AliGW; 01-09-2022 at 06:52 PM.

  10. #10
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel (Windows 11 64-bit)
    Posts
    60,630

    Re: Google Sheets: Problem with Data Validation Formula

    I've removed it for your own security as it gave access to all your files. You should only share a link that gives access to that one file.

  11. #11
    Registered User
    Join Date
    01-08-2022
    Location
    North Carolina, USA
    MS-Off Ver
    Office
    Posts
    6

    Re: Google Sheets: Problem with Data Validation Formula

    I can't figure it out. I must be the dumbest ever. Sorry to have wasted your time.

  12. #12
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    14,074

    Re: Google Sheets: Problem with Data Validation Formula

    Looking at this article, it would appear that there is not much difference between Excel and google sheets in the way of data validation except drop-down lists, which doesn't pertain to this query.
    If you could upload a sample of your query as an Excel spreadsheet then it may be possible that whatever solution works for Excel would also work for google.
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

+ 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. Problem with data validation
    By Immortal2014 in forum Excel - New Users/Basics
    Replies: 0
    Last Post: 08-20-2017, 06:49 AM
  2. [SOLVED] Data validation based on formula problem
    By nigelbloomy in forum Excel General
    Replies: 4
    Last Post: 05-12-2015, 04:11 PM
  3. [SOLVED] Multiple Data Validation Formula Problem
    By Pooger in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 02-16-2015, 05:55 PM
  4. [SOLVED] Date validation formula (problem)
    By Jhon Mustofa in forum Excel Formulas & Functions
    Replies: 23
    Last Post: 03-25-2014, 02:53 AM
  5. Problem with Formula combined with data validation
    By taylorbe in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 03-14-2014, 09:02 AM
  6. Problem with Formula based on Data Validation Selection from multiple tabs
    By warrior2411 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 09-13-2012, 05:32 PM
  7. [SOLVED] Data Validation formula problem
    By rrucksdashel in forum Excel General
    Replies: 1
    Last Post: 09-29-2005, 01:05 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