Data Validation help

Fuzzycow23

unread,

Dec 18, 2012, 11:14:51 PM12/18/12

to

Hello, I need help with a custom data validation formula for the
following scenario:

Users can input an entry in any one of these formats (# for any number
0-9):
1) ##/##'
2) ###/###'
3) ##/###'
4) ###/##'

Here is the code I have that works for format 1 and 2.

=OR(AND(COUNT(MID(H1,ROW(INDIRECT("1:"&LEN(H1))),1)+0)=4,MID(H1,3,1)="/",MID(H1,6,1)="'"),AND(COUNT(MID(H1,ROW(INDIRECT("1:"&LEN(H1))),1)+0)=6,MID(H1,4,1)="/",MID(H1,8,1)="'"))

Problem is, when I try to add more AND commands to include formats 3)
and 4), excel does not allow it because it is too many characters. Is
this possible?

--
Fuzzycow23

Spencer101

unread,

Dec 19, 2012, 2:21:15 AM12/19/12

to

Hi,

Could you explain what these values represent and what manipulation
you're attempting to achieve with the formula?

It will be much easier to provide you with a working solution if we're
armed with that info.

Thanks.

--
Spencer101

Fuzzycow23

unread,

Dec 19, 2012, 11:56:52 AM12/19/12

to

Spencer101;1608120 Wrote:
> Hi,
>
> Could you explain what these values represent and what manipulation
> you're attempting to achieve with the formula?
>
> It will be much easier to provide you with a working solution if we're
> armed with that info.
>
> Thanks.

Hi,

This is for a soccer game report, in which the numbers before the slash
are the jersey number of the scorer, and the numbers after the slash
represent the time of the goal, expressed as a minute reference, e.g. 78
= 78th minute of the game.

The organization I work for has a terrible time in getting users to
input data correctly according to this format, so it was my task to see
if we can restrict users to entering data in this format or else the
cell would reject the input. I've found the closest thing to a solution
to be the data validation feature but I'm stuck on the code.

Recap, I've got the formula working for ##/##' and ###/###', but the
formula bar seems to cap off at a certain point, so I cannot add on the
formats ##/###' and ###/##'. I can easily rework the formula so that
any 2 of these formats is included, but not all 4.

--
Fuzzycow23

Spencer101

unread,

Dec 19, 2012, 2:05:54 PM12/19/12

to

Fuzzycow23;1608130 Wrote:
> Hi,
>
> This is for a soccer game report, in which the numbers before the slash
> are the jersey number of the scorer, and the numbers after the slash
> represent the time of the goal, expressed as a minute reference, e.g. 78
> = 78th minute of the game.
>
> The organization I work for has a terrible time in getting users to
> input data correctly according to this format, so it was my task to see
> if we can restrict users to entering data in this format or else the
> cell would reject the input. I've found the closest thing to a solution
> to be the data validation feature but I'm stuck on the code.
>
> Recap, I've got the formula working for ##/##' and ###/###', but the
> formula bar seems to cap off at a certain point, so I cannot add on the
> formats ##/###' and ###/##'. I can easily rework the formula so that
> any 2 of these formats is included, but not all 4.

I can think of several solutions to this but the most appropriate one
would pretty much depend on that worksheet itself and potentially the
possibility of altering the layout slightly. That might not be
necessary but I won't know unless I can see the workbook itself (or a
dummy version of).

Any chance you could post it here or if too large for this forum,
perhaps email it to me on pubnut @ gmail . com (without space,
obviously)?

S.

--
Spencer101

Bruce Sinclair

unread,

Dec 19, 2012, 7:10:39 PM12/19/12

to

In article <Fuzzycow2...@excelbanter.com>, Fuzzycow23 <Fuzzycow2...@excelbanter.com> wrote:

>Spencer101;1608120 Wrote:
>> Could you explain what these values represent and what manipulation
>> you're attempting to achieve with the formula?
>>
>> It will be much easier to provide you with a working solution if we're
>> armed with that info.

>This is for a soccer game report, in which the numbers before the slash
>are the jersey number of the scorer, and the numbers after the slash
>represent the time of the goal, expressed as a minute reference, e.g. 78
>= 78th minute of the game.
>The organization I work for has a terrible time in getting users to
>input data correctly according to this format, so it was my task to see
>if we can restrict users to entering data in this format or else the
>cell would reject the input. I've found the closest thing to a solution
>to be the data validation feature but I'm stuck on the code.
>Recap, I've got the formula working for ##/##' and ###/###', but the
>formula bar seems to cap off at a certain point, so I cannot add on the
>formats ##/###' and ###/##'. I can easily rework the formula so that
>any 2 of these formats is included, but not all 4.

Any reason for not using 2 input cells (ie player number in A5; time in B5)
? That would simplify your problem enormously. :)

Spencer101

unread,

Dec 20, 2012, 2:24:09 AM12/20/12

to

Bruce Sinclair;1608142 Wrote:
> In article <Fuzzycow2...@excelbanter.com>, Fuzzycow23

> <Fuzzycow2...@excelbanter.com> wrote:-
> >Spencer101;1608120 Wrote: -

> >> Could you explain what these values represent and what manipulation
> >> you're attempting to achieve with the formula?
> >>
> >> It will be much easier to provide you with a working solution if
> we're

> >> armed with that info.-

> >This is for a soccer game report, in which the numbers before the
> slash
> >are the jersey number of the scorer, and the numbers after the slash
> >represent the time of the goal, expressed as a minute reference, e.g.
> 78
> >= 78th minute of the game.
> >The organization I work for has a terrible time in getting users to
> >input data correctly according to this format, so it was my task to
> see
> >if we can restrict users to entering data in this format or else the
> >cell would reject the input. I've found the closest thing to a
> solution
> >to be the data validation feature but I'm stuck on the code.
> >Recap, I've got the formula working for ##/##' and ###/###', but the
> >formula bar seems to cap off at a certain point, so I cannot add on
> the
> >formats ##/###' and ###/##'. I can easily rework the formula so that

> >any 2 of these formats is included, but not all 4.-

>
> Any reason for not using 2 input cells (ie player number in A5; time in
> B5)
> ? That would simplify your problem enormously. :)

Having seen the workbook, that was my suggestion too.
It seems the best option.

--
Spencer101