# Google Sheets: Problem with Data Validation Formula

1. ## 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

2. ## 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.

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.

3. ## 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. ## Re: Problem with Data Validation Formula

Is this for Excel or Google Sheets?

6. ## 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. ## 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. ## 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”

9. ## 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?

11. ## 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. ## 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.

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