Validation of Cells based on values in a range of other cells

[Alexjj]

Greetings everyone...1st time here, please bear with me.

I'm constructing a spreadsheet for my fellow teachers and need some guidance with the validation feature. What I'm trying to do is this:
Teachers will have a choice to enter some data in Cell B2, but if they enter data in Cell B3, B4, OR B5, they shouldn't be allowed to enter data in Cell B2.

I can successfully use the formula =if(B2="*", TRUE, FALSE) for validation in cell B3 to keep them from entering data in cell B3 if there is already a value in B2. However, in order to keep them from entering data in cell B2 I tried using =if(B3:B5="*", TRUE, FALSE) but now when I attempt to enter data in Cell B2, I get an error message that Cell B2 is restricted....does this make sense?

Please let me know if this doesn't make sense...essentially, if teachers elect to enter data in B2, they shouldn't be able to enter data in cells b3 to b5...so the values aren't counted more than once in later calculations. BUT if they enter values in cells B3 to B5, they shouldn't be able to enter data in B2.

Thank you everyone...

 

[Jonmo1]

Welcome to the board.

1. You don't need to use IF in the validation, you just need a formula that returns True Or False.
=B2="*"
will suffice.

2. Try
=COUNTA(B3:B5)=0

Hope that helps.

 

[Roseus]

Edit: Didn't see jonmo1's reply - he sounds much more knowledgable, I'd go with his answer

I haven't used the validation bit before, but this formula for B2 should do the trick:

=if(OR(B3="*",B4="*",B5="*"), TRUE, FALSE)

True would need to restrict them from using B2, because there is something in B3, B4, or B5.

 

[Alexjj]

Thank you for the quick response. I'll give these a try after I finish with lunch duty...nothing like a 120 Kindergarten kids trying to eat spaghetti...wish me luck.

Thank you though, seriously, for your help. I'm trying to make things easier for my staff by developing these spreadsheets. I'll post results later.

Alex

 

[Alexjj]

Well, I tried the suggestions, but they did not work as expected. The formula =if(OR(B3="*",B4="*",B5="*"), TRUE, FALSE) works ONLY if there is a value in B3, B4, AND B5. That is, if I put the aforementioned formula in cell B2, I can still enter values in B2 UNLESS all cells B3, B4, or B5 have values.

Please let me explain my work. Cell A2 is labeled Total Language Arts Minutes. Cell A3 is labeled Reading Minutes, Cell A4 is labeled Writing Minutes, and Cell A5 is labeled Grammar Minutes. The values for these labeled cells take place in B2 thru B5. So, if a teacher chooses to input Total Language Arts minutes instead of breaking the time down, they should only be able to input a value in Cell B2 and not be able to enter anything in cells B3 thru B5. Conversely, if a teacher chooses to break down their minutes and inputs values in B3 OR B4 OR B5, they should not be allowed to enter a value in cell B2. Does this make sense?

I just don't want their minutes being counted more than once...more of an auditing issue to get me more accurate data.

Thanks everyone.

 

[Peter_SSs]

Well, I tried the suggestions, but they did not work as expected.

I suspect that you mis-interpreted jonmo1's response. His point 2 was a suggestion for the validation in cell B2. Anyway, here is my suggestion for a Custom Data Validation formula for each of the 4 cells. I have assumed that the enties will be numeric. If not (or in any case if you want) change COUNT to COUNTA in the B2 DV.

Excel Workbook
	B
2
3
4
5
DV

#VALUE!

 

[Alexjj]

Thank you, Thank you!!! It works just as I need it to. I am pretty new to excel and want to lean more so I do apologize for misunderstanding/interpreting previous suggestions from JonMo1 and Roseus..Thank you all, you've just made my life a heck of a lot easier!!!