SOLVED

Data Validation in Excel

Occasional Contributor

Hi there,

 

I having a problem here. I want to apply data validation to some of the columns to only accept text. I have used the custom option and ISTEXT function. I then selected the columns.

 

Example: ISTEXT(C:D)

 

It is not working. When I change the range to, for example, (C2:D500), it only works in cell C2

4 Replies

@Clerik_SSS When you want to apply that rule to both columns C and D, select both of them and enter the rule =ISTEXT(C1) as the rule. Thus, the rule is set with a relative reference (i.e. without $-signs) to the top left corner of the selected range. You will note that for every cell in the range, the formula will automatically update to the selected cell. Thus, in D10 for example, the formula will show as =TEXT(D10).

 

If you want to apply it to C2:D500, select that range and enter the rule =ISTEXT(C2). Make sure that C2 is the active cell when set the rule.

@Riny_van_Eekelen 

Thank you for the response.

Your instruction is working. However, it is not taking text. When I write random words into these columns, I get an error message saying the value does not match the data validation set

best response confirmed by Clerik_SSS (Occasional Contributor)
Solution

@Clerik_SSS Try the attached file. C2:D20 should only accept text.

 

Many Thanks.