Validation - Error message if equals Left formula

[Guest]

I have a project code in column A formatted as general and the user should be
entering 4 digits dash 3 digits (0001-000). In column B and C the user needs
to break out each part of those numbers. In B they need to type 0001 and in
C they type 000. I wanted to place a data validation in B that gives the
user an error message is the number typed in B do not equal the left 4
characters in A. Same for B with the right 3 characters of A. I've tried a
few things but I can't get the formula right. Any ideas?

 

[Rick Rothstein \(MVP - VB\)]

You are having your users type a number in column A and then type the SAME
number in two parts in columns B and C? If that is what you are saying, why
not let Excel do all the work in columns B and C. Assuming the first row the
user can enter data in is A2, then put this formula in B2

=LEFT(A2,FIND("-",A2)-1)

and put this formula in C2...

=MID(A2,FIND("-",A2)+1,3)

and copy it down.

Rick


and then copy it down

 

[T. Valko]

One way...

A1 = 0001-000

You'd have to preformat B1 and C1 as TEXT.

Then as data validation formulas:

In B1:

=EXACT(LEFT(A1,4),B1)

In C1:

=EXACT(RIGHT(A1,3),C1)

 

[Guest]

The way the spreadsheet is setup the user needs to enter it twice for two
reasons. Column A where the number is combined has a data validation set to
trigger for duplicate project numbers. This number then must be hard coded
because a formula such as concatenate (to combine the two pieces) would not
work. The two pieces have to be formatted as numbers because there is a
control sheet which allows the user to enter the first set of numbers and the
MAX +1 formula helps the user know which is the next available number. For
the MAX function to work, this field must be numerical.

I really have to idiot proof this spreadsheet so I really need the
functionality. This request was to create an error message essentially
forcing the user to enter the correct number in both place so that they
match.

I'd rather have the functionality of finding the number than the error
message so that's ok. Thanks for your thoughts.

 

[Guest]

I tried this way and it worked great except that I guess I need it to be
number and not text for a MAX function I use on this field. (see reply to
Rick) But this is a great formula to keep in my repertoire. Thanks for your
help.

 

[Rick Rothstein \(MVP - VB\)]

Can you make use of a macro in your shop? If so, consider this code on the
worksheet in question...

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 1 And Target.Value Like "####-###" Then
Target.Offset(0, 1).Value = Split(Target.Value, "-")(0)
Target.Offset(0, 2).Value = Split(Target.Value, "-")(1)
End If
End Sub

You can leave out the Target.Value Like "####-###" test if your entries in
Column A are already being validated for proper "shape".

Rick

 

[Rick Rothstein \(MVP - VB\)]

IF you make them numbers, then you will lose any leading zeroes; for
example, 0012-005 will become 12 and 5 in columns B and C... with that
confuse your users?

You can have MAX work with text in needed. Here is an array formula
example...

=MAX(--(C2:C1000))

but the formula must be committed using Ctrl+Shift+Enter, not use Enter by
itself.

Rick