Excel Conditional validated data input

Question

Problem

I'm using Microsoft Excel for data collection.

I have an excel sheet with two columns.

Column A - List

The first column is a list I created with Data Validation. The choices are:

1. Cell left Blank
2. Numbers
3. Text

Column B - Conditional validated input

A column that takes user's input if not blank.

---

Question

I would like to prompt the user to input only if he selected option 1 or option 2.

If no choice is select, i.e. a blank cell, then there wouldn't be an input.

If option 1 is selected:

• The corresponding cell in column B would accept only a 3-digit number data type.

If option 2 is selected:

• The corresponding cell in column B would accept only a 10-digit text data type.

I would like the check would be based on list number, i.e. if number is 1 or if number is 2, etc.

I have a set of 10 options, so multiple conditions.

I'm using Microsoft Excel 365

Attempt

I have tried using the below formula but I'm not sure how to allow numbers or text of specific length as an input in the corresponding cell.

=IF(LEFT($A$2,1)=1,<allow numbers in B2>)

Answer 1

Since your 'options' all seem to start with an integer, you could use a lengthy SWITCH() function for each integer case.

For the two you mentioned ('1-Numbers' and '2-Text'), the function could look like this:

=IFERROR(
    SWITCH(
        VALUE(LEFT($A2,1)),
        1,AND(ISNUMBER($B2),LEN($B2)=3),
        2,LEN($B2)=10
    ),TRUE
)

What this function does is, it checks the first character in A2 through VALUE(LEFT($A2,1)), then, if it is a '1', the value in B2 has to be a number and of exactly 3 characters (AND(ISNUMBER($B2),LEN($B2)=3)), if it is a '2', the value in B2 has to have exactly 10 characters of any type (LEN($B2)=10). You can simply add more cases and corresponding limitations to that and paste the formula in a custom data validation:

The IFERROR() statement leading the function means that if the 'option' selected does not have a specified case in the data validation, any input is allowed.