How can I require a roster row to have both a name and a shift before it is accepted?
Quick answer
Use a custom data-validation rule that accepts a row only when both the name and shift cells are blank, or when both are filled. For example, if the name is in A2 and the shift is in B2, use =OR(AND($A2="",$B2=""),AND($A2<>"",$B2<>"")). Apply it to the intended entry range, adjust the references for your layout, and test blank, complete, and partly filled rows. Excel supports custom validation formulas. [S1]
The question, the details, the next step.
I’m making a roster and want each entry to have both a person’s name and a shift. How do I stop someone entering just one of those?
Set a row-completeness rule with two acceptable states: both cells are empty, or both contain entries. Anything else—a name without a shift, or a shift without a name—is incomplete and should be rejected. Excel’s Data Validation supports custom formulas for accepting or rejecting entries, including formulas using AND. [S1]
Suppose your columns are A for Name and B for Shift, with the first roster row on row 2. Use this custom formula:
=OR(AND($A2="",$B2=""),AND($A2<>"",$B2<>""))
The first part permits an intentionally unused row; the second permits a complete row. The dollar signs keep the formula checking columns A and B as it applies across the row, while the row number can adjust for later rows. A row with only one field filled satisfies neither condition.
How do I handle intentionally blank rows? I don’t want the rule to force me to fill every row in advance.
Keep the fully blank case explicitly in the formula. AND($A2="",$B2="") means a row with no name and no shift remains acceptable. That is different from allowing either cell to be blank independently, which would also allow incomplete entries.
For an Excel setup, select the entry range, such as A2:B100, open Data Validation, choose a custom formula rule, and enter the formula using the first row of that range. Check that the formula refers to the intended columns and that the row reference adjusts across the range. Excel’s documentation describes custom formulas and an “Ignore blank” setting; that setting controls blank-value handling, so don’t assume it replaces the two-cell logic. Test the actual setup in your Excel version. [S1]
Can you show me what the test cases should look like before I apply it to the whole roster?
For the example using A2 as Name and B2 as Shift, try these cases in a test row:
- A2 blank; B2 blank: accept. This is an unused row.
- A2 “Maya”; B2 “Morning”: accept. Both fields are present.
- A2 “Maya”; B2 blank: reject. The shift is missing.
- A2 blank; B2 “Morning”: reject. The name is missing.
If your roster starts on row 5 and uses columns C and D, the starting-row formula is =OR(AND($C5="",$D5=""),AND($C5<>"",$D5<>"")). Replace the example references with your layout and test the cases there too. Excel’s documentation establishes that custom validation formulas are supported; it does not verify this particular rule in your workbook, so test the actual setup. [S1]
What should I check if the rule rejects a valid entry or lets a half-filled row through?
First confirm the formula refers to the actual Name and Shift columns and uses the first row covered by the validation range. Check that the dollar signs keep the column references fixed while the row reference changes for later rows. Test all four cases above on more than one row to catch a reference that does not adjust as intended.
Also decide what “filled in” means for your roster. This example treats any nonempty cell as filled; it does not check whether a name is a real person or whether a shift is an approved option. Those are separate checks. If you need specific shift choices, design and test that rule separately without weakening the blank-row-versus-complete-row rule.
A practical checklist
Identify the Name and Shift columns and the first roster row.
Use a rule that permits both cells blank or both cells filled.
Apply the rule to the intended entry range and verify its cell references.
Test a blank row, a complete row, a name-only row, and a shift-only row.
Test a later row and separately define any rules for approved shift choices.
A few more useful answers
Does “Ignore blank” by itself require both fields?
No. It controls handling of blank values in Excel’s validation settings; it is not a substitute for explicitly expressing the two acceptable row states. [S1]
Will this rule check whether the shift is a valid shift name?
No. It checks whether both cells are empty or both are nonempty. A separate rule is needed if shifts must come from an approved list.
What if my name and shift columns are not A and B?
Replace the example references with the cells in your layout and use the first row covered by the rule as the starting row. Test the rule on several rows in your workbook.
Sources
References used for this guide.
- [S1] Apply data validation to cells in Excel support.microsoft.com · Checked