Re: Validation - Error message if equals Left formula

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

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


"JICDB" <JICDB@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message news:5971B2E2-132E-45EE-8D28-2D1AC172F405@xxxxxxxxxxxxxxxx
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.

"T. Valko" wrote:

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)

--
Biff
Microsoft Excel MVP


"JICDB" <JICDB@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:19F12FCC-BA38-4496-8D99-626CFEF3C315@xxxxxxxxxxxxxxxx
>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?




.

Relevant Pages

  • Re: Solaris 9 username longer that 8 characters
    ... I dont want to end up in a cauldron!! ... that format only prints the first 8 characters of a username. ... < This line left intentionally blank to confuse you. ...
    (comp.unix.solaris)
  • Re: Column space requirements - VARCHAR2 versus NUMBER
    ... that will contain leading zeroes" mean ... The length including leading zeroes is variable and all digits are ... Leading zeros... ... This is 10 numeric characters in one column in a 300 million row ...
    (comp.databases.oracle.misc)
  • Re: Entry length
    ... If using Windows Accessibility Options plays a "beep" on Data Validation ... 1)Set the cell fonts to a Fixed Font (eg Courier New) ... 3)Set Data Validation to only allow that number of characters ... people cannot see that the entry is truncated by the entry in the cell ...
    (microsoft.public.excel)
  • Re: Validation - Error message if equals Left formula
    ... entering 4 digits dash 3 digits. ... 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 ...
    (microsoft.public.excel.worksheet.functions)
  • Re: Linked table shows no data
    ... text files from non-Windows computers sometimes use different characters for ... and this can confuse Access's import routine. ... not showing for this linked table? ...
    (microsoft.public.access.externaldata)