Hi all,

a really picky question, why does the data validation allow a space
after the list value?


for instance, I have data validation to allow "yes" or "no" and I have
some other sumproducts looking to these cells to give me figures, but
it will allow you to put "yes " or "no " which the sumproduct wont pick



up with the blank space in.


can this be prevented? and why does it allow the blank space?


Duncan

You can still count it with

=SUMPRODUCT(--(LEFT(F1:F100,3)="yes"))

--
HTH

Bob Phillips

(remove xxx from email address if mailing direct)

"Duncan" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> Hi all,
>
> a really picky question, why does the data validation allow a space
> after the list value?
>
>
> for instance, I have data validation to allow "yes" or "no" and I have
> some other sumproducts looking to these cells to give me figures, but
> it will allow you to put "yes " or "no " which the sumproduct wont pick
>
>
>
> up with the blank space in.
>
>
> can this be prevented? and why does it allow the blank space?
>
>
> Duncan
>

Bob,

thats a great solution, I will bear it in mind for future use and i
have changed most of my formulas to include it now.

I still wonder, does anyone know why it will allow a space in the list?
surely it should pop up as a disallowed value?

anyway, its working great now thanks to Bob.

Many thanks again

Duncan

(my formula now looks like
=SUMPRODUCT(--(LEFT(data!G2:G65536,3)="yes"),--(data!H2:H65536)) in
case it might be helpful to anyone)

Duncan,

two things.

In SP, if there is no condition being tested you can omit the double unary
on that range

=SUMPRODUCT(--(LEFT(data!G2:G65536,3)="yes"),data!H2:H65536)

but more importantly, if you just have one condition, you could use SUMIF

=SUMIF(data!G:G,"yes*",data!H:H)

and notice that SUMIF handles complete columns.

--
HTH

Bob Phillips

(remove xxx from email address if mailing direct)

"Duncan" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> Bob,
>
> thats a great solution, I will bear it in mind for future use and i
> have changed most of my formulas to include it now.
>
> I still wonder, does anyone know why it will allow a space in the list?
> surely it should pop up as a disallowed value?
>
> anyway, its working great now thanks to Bob.
>
> Many thanks again
>
> Duncan
>
> (my formula now looks like
> =SUMPRODUCT(--(LEFT(data!G2:G65536,3)="yes"),--(data!H2:H65536)) in
> case it might be helpful to anyone)
>

*Don't* individually enter your "allow" criteria in the 'Source' box!

Instead, create an allowed list in an out-of-the-way section of your sheet,
and then refer to that list range in the 'Source' box.

For example:
Z1 = Yes
Z2 = No

In the 'Source' box, enter"
=Z1:Z2

NOW,
"Yes"
is allowed,
And
"Yes "
is NOT.

You could also assign a name to that allowed list, same as if you wanted to
place that list on another sheet.

Select Z1 to Z2,
Click in the 'Name' box,
Type a short name. such as "List" (no quotes),
Hit <Enter>

Then, in the 'Source' box, enter:

=list

And you'll have the same protection against <Spaces>.
--

HTH,

RD
=====================================================
Please keep all correspondence within the Group, so all may benefit!
=====================================================


"Duncan" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
Bob,

thats a great solution, I will bear it in mind for future use and i
have changed most of my formulas to include it now.

I still wonder, does anyone know why it will allow a space in the list?
surely it should pop up as a disallowed value?

anyway, its working great now thanks to Bob.

Many thanks again

Duncan

(my formula now looks like
=SUMPRODUCT(--(LEFT(data!G2:G65536,3)="yes"),--(data!H2:H65536)) in
case it might be helpful to anyone)

RD,

Thank you for this, ive checked and it would work perfectly, I have
already changed all of my formulas and it is too late in the day for me
to change it all again but I will definately remeber to do it this way
next time!

(*I wonder why excel can be so good, yet requires user-manipulation to
perform to its best*), why should it not take your word for it in the
source box?! lol

Many thanks

Duncan