conditional formula

[Serge]

I need a solution for entering data in any one cell out of seven in a range
in one row. I need to be able to select any cell but only one, if data is
entered in an other cell in the same range, it needs to create an error
message

[vezerid]

Serge,
The formula

=COUNTBLANK(A1:G1)<=6

Will return TRUE if you fill more than one cell in A1:G1. This formula
can be used in Data Validation (Custom), in Conditional Formatting
(with Formula Is or in a single cell next to the data as in:

=IF(COUNTBLANK(A1:G1)<=6, "", "Not more than one entry")

HTH
Kostis Vezerides

[Bob Phillips]

You could use data validation with a custom formula of say

=COUNTA($M$4:$M$10)<2

which will trap entry on input.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Serge" <Serge@discussions.microsoft.com> wrote in message
news:A28FF5F6-37E7-4607-8CC7-5BFF8DF03C1F@microsoft.com...
> I need a solution for entering data in any one cell out of seven in a
range
> in one row. I need to be able to select any cell but only one, if data
is
> entered in an other cell in the same range, it needs to create an error
> message

[Serge]

Hello Kostis,
Unable to make it work

"vezerid" wrote:

> Serge,
> The formula
>
> =COUNTBLANK(A1:G1)<=6
>
> Will return TRUE if you fill more than one cell in A1:G1. This formula
> can be used in Data Validation (Custom), in Conditional Formatting
> (with Formula Is or in a single cell next to the data as in:
>
> =IF(COUNTBLANK(A1:G1)<=6, "", "Not more than one entry")
>
> HTH
> Kostis Vezerides
>
>

[Serge]

Hello Bob,
Unable to make this one work as well

"Bob Phillips" wrote:

> You could use data validation with a custom formula of say
>
> =COUNTA($M$4:$M$10)<2
>
> which will trap entry on input.
>
> --
> HTH
>
> Bob Phillips
>
> (remove nothere from email address if mailing direct)
>
> "Serge" <Serge@discussions.microsoft.com> wrote in message
> news:A28FF5F6-37E7-4607-8CC7-5BFF8DF03C1F@microsoft.com...
> > I need a solution for entering data in any one cell out of seven in a
> range
> > in one row. I need to be able to select any cell but only one, if data
> is
> > entered in an other cell in the same range, it needs to create an error
> > message
>
>
>

[Bob Phillips]

Works for me. I posted an example at http://cjoint.com/?btubHzBJgT

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Serge" <Serge@discussions.microsoft.com> wrote in message
news:B90C9FAB-E0EB-436D-A981-FCD097292A94@microsoft.com...
> Hello Bob,
> Unable to make this one work as well
>
> "Bob Phillips" wrote:
>
> > You could use data validation with a custom formula of say
> >
> > =COUNTA($M$4:$M$10)<2
> >
> > which will trap entry on input.
> >
> > --
> > HTH
> >
> > Bob Phillips
> >
> > (remove nothere from email address if mailing direct)
> >
> > "Serge" <Serge@discussions.microsoft.com> wrote in message
> > news:A28FF5F6-37E7-4607-8CC7-5BFF8DF03C1F@microsoft.com...
> > > I need a solution for entering data in any one cell out of seven in a
> > range
> > > in one row. I need to be able to select any cell but only one, if
data
> > is
> > > entered in an other cell in the same range, it needs to create an
error
> > > message
> >
> >
> >

[Serge]

Hello Bob,
I tried one more time, this time it works. I needed to copy the formula to
adjacent cell in that row range.
But now it seems I'm not able to copy the formula in other rows without
having to change the row number every time.

"Bob Phillips" wrote:

> Works for me. I posted an example at http://cjoint.com/?btubHzBJgT
>
> --
> HTH
>
> Bob Phillips
>
> (remove nothere from email address if mailing direct)
>
> "Serge" <Serge@discussions.microsoft.com> wrote in message
> news:B90C9FAB-E0EB-436D-A981-FCD097292A94@microsoft.com...
> > Hello Bob,
> > Unable to make this one work as well
> >
> > "Bob Phillips" wrote:
> >
> > > You could use data validation with a custom formula of say
> > >
> > > =COUNTA($M$4:$M$10)<2
> > >
> > > which will trap entry on input.
> > >
> > > --
> > > HTH
> > >
> > > Bob Phillips
> > >
> > > (remove nothere from email address if mailing direct)
> > >
> > > "Serge" <Serge@discussions.microsoft.com> wrote in message
> > > news:A28FF5F6-37E7-4607-8CC7-5BFF8DF03C1F@microsoft.com...
> > > > I need a solution for entering data in any one cell out of seven in a
> > > range
> > > > in one row. I need to be able to select any cell but only one, if
> data
> > > is
> > > > entered in an other cell in the same range, it needs to create an
> error
> > > > message
> > >
> > >
> > >
>
>
>