Data Validation - mixture of options

Hi all

I want to set data validation on a cell so that it will accept the following
(and only the following) text strings:
(1) "BR" (literally)
or
(2) "NT" (literally)
or
(3) "nA" where n is any positive integral numerical value including zero and
A may take any of the values "L", "P", "T", "V" or "Y"
or
(4) "An" where n is any positive integral numerical value including zero and
A may take either of the values "K" or "D".

Is this possible, please, and if so how? thanks

--
Return email address is not as DEEP as it appears

Jack,

For cell A1, use Data / Validation, Allow - Custom, and in the formula
area, enter (take out any extra line returns before using this):

=OR(A1="BR",A1="NT",AND(NOT(ISERROR(FIND(RIGHT(A1,1),"LPTVY"))),NOT(ISERROR(
VALUE(LEFT(A1,LEN(A1)-1))))),AND(NOT(ISERROR(FIND(LEFT(A1,1),"KD"))),NOT(ISE
RROR(VALUE(RIGHT(A1,LEN(A1)-1))))))

HTH,
Bernie
MS Excel MVP


"Jack Sheet" <mind-the-gap@DEEPblueyonder.co.uk> wrote in message
news:uGui%23RRXFHA.3620@TK2MSFTNGP09.phx.gbl...
> Hi all
>
> I want to set data validation on a cell so that it will accept the
following
> (and only the following) text strings:
> (1) "BR" (literally)
> or
> (2) "NT" (literally)
> or
> (3) "nA" where n is any positive integral numerical value including zero
and
> A may take any of the values "L", "P", "T", "V" or "Y"
> or
> (4) "An" where n is any positive integral numerical value including zero
and
> A may take either of the values "K" or "D".
>
> Is this possible, please, and if so how? thanks
>
> --
> Return email address is not as DEEP as it appears
>
>

Actually, I forgot about the positive integral part.

Change the Data validation formula to

=B1

and in B1, enter the formula

=OR(A1="BR",A1="NT",AND(NOT(ISERROR(FIND(RIGHT(A1,1),"LPTVY"))),IF(NOT(ISERR
OR(VALUE(LEFT(A1,LEN(A1)-1)))),AND(VALUE(LEFT(A1,LEN(A1)-1))>0,INT(VALUE(LEF
T(A1,LEN(A1)-1)))=VALUE(LEFT(A1,LEN(A1)-1))),FALSE)),AND(NOT(ISERROR(FIND(LE
FT(A1,1),"KD"))),IF(NOT(ISERROR(VALUE(RIGHT(A1,LEN(A1)-1)))),AND(VALUE(RIGHT
(A1,LEN(A1)-1))>0,INT(VALUE(RIGHT(A1,LEN(A1)-1)))=VALUE(RIGHT(A1,LEN(A1)-1))
),FALSE)))

You need to use cell B1 because the formula is longer than the data
validation formula length limit.

HTH,
Bernie
MS Excel MVP


"Jack Sheet" <mind-the-gap@DEEPblueyonder.co.uk> wrote in message
news:uGui%23RRXFHA.3620@TK2MSFTNGP09.phx.gbl...
> Hi all
>
> I want to set data validation on a cell so that it will accept the
following
> (and only the following) text strings:
> (1) "BR" (literally)
> or
> (2) "NT" (literally)
> or
> (3) "nA" where n is any positive integral numerical value including zero
and
> A may take any of the values "L", "P", "T", "V" or "Y"
> or
> (4) "An" where n is any positive integral numerical value including zero
and
> A may take either of the values "K" or "D".
>
> Is this possible, please, and if so how? thanks
>
> --
> Return email address is not as DEEP as it appears
>
>

That worked fine, thanks (I just changed the ">" to ">=" to enable zeros to
count as TRUE).
Regards

"Bernie Deitrick" <deitbe @ consumer dot org> wrote in message
news:%238Ncf6TXFHA.2288@TK2MSFTNGP14.phx.gbl...
> Actually, I forgot about the positive integral part.
>
> Change the Data validation formula to
>
> =B1
>
> and in B1, enter the formula
>
>
=OR(A1="BR",A1="NT",AND(NOT(ISERROR(FIND(RIGHT(A1,1),"LPTVY"))),IF(NOT(ISERR
>
OR(VALUE(LEFT(A1,LEN(A1)-1)))),AND(VALUE(LEFT(A1,LEN(A1)-1))>0,INT(VALUE(LEF
>
T(A1,LEN(A1)-1)))=VALUE(LEFT(A1,LEN(A1)-1))),FALSE)),AND(NOT(ISERROR(FIND(LE
>
FT(A1,1),"KD"))),IF(NOT(ISERROR(VALUE(RIGHT(A1,LEN(A1)-1)))),AND(VALUE(RIGHT
>
(A1,LEN(A1)-1))>0,INT(VALUE(RIGHT(A1,LEN(A1)-1)))=VALUE(RIGHT(A1,LEN(A1)-1))
> ),FALSE)))
>
> You need to use cell B1 because the formula is longer than the data
> validation formula length limit.
>
> HTH,
> Bernie
> MS Excel MVP
>
>
> "Jack Sheet" <mind-the-gap@DEEPblueyonder.co.uk> wrote in message
> news:uGui%23RRXFHA.3620@TK2MSFTNGP09.phx.gbl...
> > Hi all
> >
> > I want to set data validation on a cell so that it will accept the
> following
> > (and only the following) text strings:
> > (1) "BR" (literally)
> > or
> > (2) "NT" (literally)
> > or
> > (3) "nA" where n is any positive integral numerical value including zero
> and
> > A may take any of the values "L", "P", "T", "V" or "Y"
> > or
> > (4) "An" where n is any positive integral numerical value including zero
> and
> > A may take either of the values "K" or "D".
> >
> > Is this possible, please, and if so how? thanks
> >
> > --
> > Return email address is not as DEEP as it appears
> >
> >
>
>

Just one minor problem:
As the formula in B1 refers to the address of the cell to which the data
validation applies, it seems that I am going to have to copy this formula
down to each row in column B for which a corresponding entry is to be made
in column A. I would have preferred as solution that just requires the
formula to be stored once, simply to reduce the processing overhead on the
workbook. I can live with it as it stands, but is there a way around that?


"Bernie Deitrick" <deitbe @ consumer dot org> wrote in message
news:%238Ncf6TXFHA.2288@TK2MSFTNGP14.phx.gbl...
> Actually, I forgot about the positive integral part.
>
> Change the Data validation formula to
>
> =B1
>
> and in B1, enter the formula
>
>
=OR(A1="BR",A1="NT",AND(NOT(ISERROR(FIND(RIGHT(A1,1),"LPTVY"))),IF(NOT(ISERR
>
OR(VALUE(LEFT(A1,LEN(A1)-1)))),AND(VALUE(LEFT(A1,LEN(A1)-1))>0,INT(VALUE(LEF
>
T(A1,LEN(A1)-1)))=VALUE(LEFT(A1,LEN(A1)-1))),FALSE)),AND(NOT(ISERROR(FIND(LE
>
FT(A1,1),"KD"))),IF(NOT(ISERROR(VALUE(RIGHT(A1,LEN(A1)-1)))),AND(VALUE(RIGHT
>
(A1,LEN(A1)-1))>0,INT(VALUE(RIGHT(A1,LEN(A1)-1)))=VALUE(RIGHT(A1,LEN(A1)-1))
> ),FALSE)))
>
> You need to use cell B1 because the formula is longer than the data
> validation formula length limit.
>
> HTH,
> Bernie
> MS Excel MVP
>
>
> "Jack Sheet" <mind-the-gap@DEEPblueyonder.co.uk> wrote in message
> news:uGui%23RRXFHA.3620@TK2MSFTNGP09.phx.gbl...
> > Hi all
> >
> > I want to set data validation on a cell so that it will accept the
> following
> > (and only the following) text strings:
> > (1) "BR" (literally)
> > or
> > (2) "NT" (literally)
> > or
> > (3) "nA" where n is any positive integral numerical value including zero
> and
> > A may take any of the values "L", "P", "T", "V" or "Y"
> > or
> > (4) "An" where n is any positive integral numerical value including zero
> and
> > A may take either of the values "K" or "D".
> >
> > Is this possible, please, and if so how? thanks
> >
> > --
> > Return email address is not as DEEP as it appears
> >
> >
>
>

Jack,

You would need to use the worksheet change event - VBA code, so you would
need to be able to have code, and have macros enabled (some folks don't like
that.)

Besides, it really doesn't add to the processing overhead. Excel only calc's
the cells that are directly affected by a change. You could have 1000
cells, and change 1 of those, and only the corresponding cell in column B
will be calc'd. Using VBA will actually slow your file down more.

HTH,
Bernie
MS Excel MVP


"Jack Sheet" <mind-the-gap@DEEPblueyonder.co.uk> wrote in message
news:O8v3fXUXFHA.3620@TK2MSFTNGP09.phx.gbl...
> Just one minor problem:
> As the formula in B1 refers to the address of the cell to which the data
> validation applies, it seems that I am going to have to copy this formula
> down to each row in column B for which a corresponding entry is to be made
> in column A. I would have preferred as solution that just requires the
> formula to be stored once, simply to reduce the processing overhead on the
> workbook. I can live with it as it stands, but is there a way around
that?
>
>
> "Bernie Deitrick" <deitbe @ consumer dot org> wrote in message
> news:%238Ncf6TXFHA.2288@TK2MSFTNGP14.phx.gbl...
> > Actually, I forgot about the positive integral part.
> >
> > Change the Data validation formula to
> >
> > =B1
> >
> > and in B1, enter the formula
> >
> >
>
=OR(A1="BR",A1="NT",AND(NOT(ISERROR(FIND(RIGHT(A1,1),"LPTVY"))),IF(NOT(ISERR
> >
>
OR(VALUE(LEFT(A1,LEN(A1)-1)))),AND(VALUE(LEFT(A1,LEN(A1)-1))>0,INT(VALUE(LEF
> >
>
T(A1,LEN(A1)-1)))=VALUE(LEFT(A1,LEN(A1)-1))),FALSE)),AND(NOT(ISERROR(FIND(LE
> >
>
FT(A1,1),"KD"))),IF(NOT(ISERROR(VALUE(RIGHT(A1,LEN(A1)-1)))),AND(VALUE(RIGHT
> >
>
(A1,LEN(A1)-1))>0,INT(VALUE(RIGHT(A1,LEN(A1)-1)))=VALUE(RIGHT(A1,LEN(A1)-1))
> > ),FALSE)))
> >
> > You need to use cell B1 because the formula is longer than the data
> > validation formula length limit.
> >
> > HTH,
> > Bernie
> > MS Excel MVP
> >
> >
> > "Jack Sheet" <mind-the-gap@DEEPblueyonder.co.uk> wrote in message
> > news:uGui%23RRXFHA.3620@TK2MSFTNGP09.phx.gbl...
> > > Hi all
> > >
> > > I want to set data validation on a cell so that it will accept the
> > following
> > > (and only the following) text strings:
> > > (1) "BR" (literally)
> > > or
> > > (2) "NT" (literally)
> > > or
> > > (3) "nA" where n is any positive integral numerical value including
zero
> > and
> > > A may take any of the values "L", "P", "T", "V" or "Y"
> > > or
> > > (4) "An" where n is any positive integral numerical value including
zero
> > and
> > > A may take either of the values "K" or "D".
> > >
> > > Is this possible, please, and if so how? thanks
> > >
> > > --
> > > Return email address is not as DEEP as it appears
> > >
> > >
> >
> >
>
>

Thanks again. I agree that I would rather do without the VBA

"Bernie Deitrick" <deitbe @ consumer dot org> wrote in message
news:eKsRWjUXFHA.584@TK2MSFTNGP15.phx.gbl...
> Jack,
>
> You would need to use the worksheet change event - VBA code, so you would
> need to be able to have code, and have macros enabled (some folks don't
like
> that.)
>
> Besides, it really doesn't add to the processing overhead. Excel only
calc's
> the cells that are directly affected by a change. You could have 1000
> cells, and change 1 of those, and only the corresponding cell in column B
> will be calc'd. Using VBA will actually slow your file down more.
>
> HTH,
> Bernie
> MS Excel MVP
>
>
> "Jack Sheet" <mind-the-gap@DEEPblueyonder.co.uk> wrote in message
> news:O8v3fXUXFHA.3620@TK2MSFTNGP09.phx.gbl...
> > Just one minor problem:
> > As the formula in B1 refers to the address of the cell to which the data
> > validation applies, it seems that I am going to have to copy this
formula
> > down to each row in column B for which a corresponding entry is to be
made
> > in column A. I would have preferred as solution that just requires the
> > formula to be stored once, simply to reduce the processing overhead on
the
> > workbook. I can live with it as it stands, but is there a way around
> that?
> >
> >
> > "Bernie Deitrick" <deitbe @ consumer dot org> wrote in message
> > news:%238Ncf6TXFHA.2288@TK2MSFTNGP14.phx.gbl...
> > > Actually, I forgot about the positive integral part.
> > >
> > > Change the Data validation formula to
> > >
> > > =B1
> > >
> > > and in B1, enter the formula
> > >
> > >
> >
>
=OR(A1="BR",A1="NT",AND(NOT(ISERROR(FIND(RIGHT(A1,1),"LPTVY"))),IF(NOT(ISERR
> > >
> >
>
OR(VALUE(LEFT(A1,LEN(A1)-1)))),AND(VALUE(LEFT(A1,LEN(A1)-1))>0,INT(VALUE(LEF
> > >
> >
>
T(A1,LEN(A1)-1)))=VALUE(LEFT(A1,LEN(A1)-1))),FALSE)),AND(NOT(ISERROR(FIND(LE
> > >
> >
>
FT(A1,1),"KD"))),IF(NOT(ISERROR(VALUE(RIGHT(A1,LEN(A1)-1)))),AND(VALUE(RIGHT
> > >
> >
>
(A1,LEN(A1)-1))>0,INT(VALUE(RIGHT(A1,LEN(A1)-1)))=VALUE(RIGHT(A1,LEN(A1)-1))
> > > ),FALSE)))
> > >
> > > You need to use cell B1 because the formula is longer than the data
> > > validation formula length limit.
> > >
> > > HTH,
> > > Bernie
> > > MS Excel MVP
> > >
> > >
> > > "Jack Sheet" <mind-the-gap@DEEPblueyonder.co.uk> wrote in message
> > > news:uGui%23RRXFHA.3620@TK2MSFTNGP09.phx.gbl...
> > > > Hi all
> > > >
> > > > I want to set data validation on a cell so that it will accept the
> > > following
> > > > (and only the following) text strings:
> > > > (1) "BR" (literally)
> > > > or
> > > > (2) "NT" (literally)
> > > > or
> > > > (3) "nA" where n is any positive integral numerical value including
> zero
> > > and
> > > > A may take any of the values "L", "P", "T", "V" or "Y"
> > > > or
> > > > (4) "An" where n is any positive integral numerical value including
> zero
> > > and
> > > > A may take either of the values "K" or "D".
> > > >
> > > > Is this possible, please, and if so how? thanks
> > > >
> > > > --
> > > > Return email address is not as DEEP as it appears
> > > >
> > > >
> > >
> > >
> >
> >
>
>