Drop down list which has a formula to restrict text format

[rippedit123]

Hi everyone,

I'm desperately hoping someone could help me out as I'm really struggling with this issue.

I'm trying to create a spreadsheet where my friends can choose something from a drop-down box and depending on their choice, they can only enter text/numbers in a fixed format.

So for example, Cell A1 has a drop down list with two options:

REF1

REF2

If the user chooses REF1, then they can only enter the following text/number format in cell B1: X12345
If the user chooses REF2, then they can only enter the following text/number format in cell B1: 123X993456789

The text and numbers highlighted in blue and red are fixed.

Can this be done? If so, how?

Thanks for helping.

 

[Eric W]

You can use Data Validation. Select B1. On the Data tab, click Data Validation > Custom > and enter this formula:

=IFERROR(IF(A1="REF1",AND(LEFT(B1)="x",LEN(B1)=6,ISNUMBER(MID(B1,2,5)+0)),IF(A1="REF2",AND(LEN(B1)=13,MID(B1,4,3)="X99",ISNUMBER(LEFT(B1,3)+0),ISNUMBER(RIGHT(B1,7)+0)))),FALSE)

Be aware however, that someone can copy a value from another cell and paste it into B1 and it will accept it.

 

[rippedit123]

Thanks very much Eric. That seems to be work just fine. I hope your not going to mind, but in order to help you to understand what I wanted to create, I had to give you a very simple example. I underestimated how quickly you'll figure it out.

In reality, I need to have a drop down for 3 items:

REF 1 = X123456789
REF 2 = X1234567890
REF 3 = 123X12345678

Is there any chance of adjust the formula accordingly?

Thank you.

 

[Eric W]

Try:

=IFERROR(IF(A1="REF1",AND(LEFT(B1)="x",LEN(B1)=10,ISNUMBER(MID(B1,2,9)+0)),IF(A1="REF2",AND(LEFT(B1)="x",LEN(B1)=11,ISNUMBER(MID(B1,2,10)+0)),IF(A1="REF3",AND(LEN(B1)=12,MID(B1,4,3)="X12",ISNUMBER(LEFT(B1,3)+0),ISNUMBER(RIGHT(B1,6)+0))))),FALSE)

I guessed that the starting x in Ref1 and Ref2 are fixed, and the X12 in Ref3 is fixed. If not, let me know.

Also, the formula could be simplified a bit if your values really are "REF 1", "REF 2", and "REF 3". I didn't know if you're just using those as placeholders and you actually use a different value.

 

[Peter_SSs]

Thanks very much Eric. That seems to be work just fine.

It actually still allows invalid entries. For example, if A1 is REF1 you can enter these invalid values in B1
X0E+00
XAPR91
Also not sure if this is a valid entry with a lower case x? x666666

There are similar issues with the REF2 validation.

I may have alternative suggestions but your requirements are not actually clear.

a) We started with REF1, now we have REF 1
What are the actual exact values allowed in A1?

b) Originally, the REF2 validation required "X99" in it. Has that requirement disappeared? Is it now just that an "X" is required at a particular location in each of the validations?

c) Does it matter if the "X" is a lower case "x"?

 

[rippedit123]

Thanks very much for all your help Eric & Peter. Unfortunately I think Peter maybe right that the formula is not working and I can still enter of data.

To answer your questions:

1. REF 1,2,3 are placeholders for other text, which I hope you can forgive me, can't be revealed on the forum unless I had your permission to PM it directly to you.
2. REF 1,2,3 are simply dropdown options that I can then change to anything...for example APPLES, ORANGES, PEARS etc.
3. The only text that needs to be fixed is the one in Red & Blue Bold text.
4. Cell A1 is simply a drop down of the 3 REFS...it can be anything such as REFERENCE 1, REFERENCE 2, REFERENCE 3, as far as I'm concerned, so long as when I choose for example REF1, then it restricts the text pattern in cell B1.
5. The pattern is not case-sensitive.
6. Well spotted, yes the X99 is simply an X. Sorry about the confusion.

Also is there any method where I can stop people from pasting data values into Excel?

I look forward to your replies.

 

[Peter_SSs]

It doesn't really matter what the drop-down values are, you'll just need to adjust the suggested formula for your actual values.
I am using the patterns as shown in post 3 and assuming that it is only the "X" that is the fixed text/position, the rest should be digits.

Try this in your DV for cell B1

=AND(LEN(B1)=IF(A1="REF1",10,IF(A1="REF2",11,IF(A1="REF3",12,0))),B1=IF(A1="REF1",TEXT(RIGHT(B1,9),"X000000000"),IF(A1="REF2",TEXT(RIGHT(B1,10),"X0000000000"),TEXT(LEFT(B1,3)&RIGHT(B1,8),"000X00000000"))))

Also is there any method where I can stop people from pasting data values into Excel?

That is somewhat trickier but there are suggestions around. Here is one (I haven't tested it), but Googling or searching this forum should turn up plenty more.