I have 3 cells called "overtime" Traveltime"and "Servicetime"
Those cells are in a single row.
I am looking for a way so that only ONE of those cells are allowed to be
filled with the letter "J". So only one cell i allowed. When already a cell
is filled, the a message must be poped up like "already one value entered,
only one value is allowed".

I hope somebody can help me out here!!

thanks

Conditional FormattingFormula is:

=COUNTIF($A$1:$C$1,"J")=1

Edit the error message to suit.


Gord Dibben MS Excel MVP

On Thu, 9 Jul 2009 06:36:01 -0700, Peter Jonkers
wrote:

I have 3 cells called "overtime" Traveltime"and "Servicetime"
Those cells are in a single row.
I am looking for a way so that only ONE of those cells are allowed to be
filled with the letter "J". So only one cell i allowed. When already a cell
is filled, the a message must be poped up like "already one value entered,
only one value is allowed".

I hope somebody can help me out here!!

thanks

thanks for the quick response.
I

The thing is .. I am not an Excel "gooroe".
How can u use this formula and if the condition is true...,how can i
generate a popup for the user

and another question is..i have about 40 rows that i have to use this code
for..how do I use it then

thanks again


cheers

Peter

"Gord Dibben" wrote:

Conditional FormattingFormula is:

=COUNTIF($A$1:$C$1,"J")=1

Edit the error message to suit.


Gord Dibben MS Excel MVP

On Thu, 9 Jul 2009 06:36:01 -0700, Peter Jonkers
wrote:

I have 3 cells called "overtime" Traveltime"and "Servicetime"
Those cells are in a single row.
I am looking for a way so that only ONE of those cells are allowed to be
filled with the letter "J". So only one cell i allowed. When already a cell
is filled, the a message must be poped up like "already one value entered,
only one value is allowed".

I hope somebody can help me out here!!

thanks

Or...how can i prevent that the other cells are filled


"Gord Dibben" wrote:

Conditional FormattingFormula is:

=COUNTIF($A$1:$C$1,"J")=1

Edit the error message to suit.


Gord Dibben MS Excel MVP

On Thu, 9 Jul 2009 06:36:01 -0700, Peter Jonkers
wrote:

I have 3 cells called "overtime" Traveltime"and "Servicetime"
Those cells are in a single row.
I am looking for a way so that only ONE of those cells are allowed to be
filled with the letter "J". So only one cell i allowed. When already a cell
is filled, the a message must be poped up like "already one value entered,
only one value is allowed".

I hope somebody can help me out here!!

thanks

You don't have to be an Excel guru to do this.

Select A1:C40 and enter this formula in FormatConditional
FormattingFormula is:

=COUNTIF($A1:$C1,"J")=1

Note the change in $ signs which makes the row reference relative.

If any of the cells in each row of columns A:C has a "J" then users will be
prevented from entering another "J" in that row.


Gord


On Thu, 9 Jul 2009 08:13:02 -0700, Peter Jonkers
wrote:

thanks for the quick response.
I

The thing is .. I am not an Excel "gooroe".
How can u use this formula and if the condition is true...,how can i
generate a popup for the user

and another question is..i have about 40 rows that i have to use this code
for..how do I use it then

thanks again


cheers

Peter

"Gord Dibben" wrote:

Conditional FormattingFormula is:

=COUNTIF($A$1:$C$1,"J")=1

Edit the error message to suit.


Gord Dibben MS Excel MVP

On Thu, 9 Jul 2009 06:36:01 -0700, Peter Jonkers
wrote:

I have 3 cells called "overtime" Traveltime"and "Servicetime"
Those cells are in a single row.
I am looking for a way so that only ONE of those cells are allowed to be
filled with the letter "J". So only one cell i allowed. When already a cell
is filled, the a message must be poped up like "already one value entered,
only one value is allowed".

I hope somebody can help me out here!!

thanks

Thanks again ,

I tried this but i am still able to fill te cells with "j".
I did exactly what you did..is this maybe a excel 2007 issue?

cheers

"Gord Dibben" wrote:

You don't have to be an Excel guru to do this.

Select A1:C40 and enter this formula in FormatConditional
FormattingFormula is:

=COUNTIF($A1:$C1,"J")=1

Note the change in $ signs which makes the row reference relative.

If any of the cells in each row of columns A:C has a "J" then users will be
prevented from entering another "J" in that row.


Gord


On Thu, 9 Jul 2009 08:13:02 -0700, Peter Jonkers
wrote:

thanks for the quick response.
I

The thing is .. I am not an Excel "gooroe".
How can u use this formula and if the condition is true...,how can i
generate a popup for the user

and another question is..i have about 40 rows that i have to use this code
for..how do I use it then

thanks again


cheers

Peter

"Gord Dibben" wrote:

Conditional FormattingFormula is:

=COUNTIF($A$1:$C$1,"J")=1

Edit the error message to suit.


Gord Dibben MS Excel MVP

On Thu, 9 Jul 2009 06:36:01 -0700, Peter Jonkers
wrote:

I have 3 cells called "overtime" Traveltime"and "Servicetime"
Those cells are in a single row.
I am looking for a way so that only ONE of those cells are allowed to be
filled with the letter "J". So only one cell i allowed. When already a cell
is filled, the a message must be poped up like "already one value entered,
only one value is allowed".

I hope somebody can help me out here!!

thanks

Oh crap............slaps forehead and starts again<g

Sorry for the problems I have caused you with my inattention.

I gave you the the wrong function.

DataValidationCustom

Enter =COUNTIF($A1:$C1,"J")=1


Gord

On Thu, 9 Jul 2009 22:53:01 -0700, Peter Jonkers
wrote:

Thanks again ,

I tried this but i am still able to fill te cells with "j".
I did exactly what you did..is this maybe a excel 2007 issue?

cheers

"Gord Dibben" wrote:

You don't have to be an Excel guru to do this.

Select A1:C40 and enter this formula in FormatConditional
FormattingFormula is:

=COUNTIF($A1:$C1,"J")=1

Note the change in $ signs which makes the row reference relative.

If any of the cells in each row of columns A:C has a "J" then users will be
prevented from entering another "J" in that row.


Gord


On Thu, 9 Jul 2009 08:13:02 -0700, Peter Jonkers
wrote:

thanks for the quick response.
I

The thing is .. I am not an Excel "gooroe".
How can u use this formula and if the condition is true...,how can i
generate a popup for the user

and another question is..i have about 40 rows that i have to use this code
for..how do I use it then

thanks again


cheers

Peter

"Gord Dibben" wrote:

Conditional FormattingFormula is:

=COUNTIF($A$1:$C$1,"J")=1

Edit the error message to suit.


Gord Dibben MS Excel MVP

On Thu, 9 Jul 2009 06:36:01 -0700, Peter Jonkers
wrote:

I have 3 cells called "overtime" Traveltime"and "Servicetime"
Those cells are in a single row.
I am looking for a way so that only ONE of those cells are allowed to be
filled with the letter "J". So only one cell i allowed. When already a cell
is filled, the a message must be poped up like "already one value entered,
only one value is allowed".

I hope somebody can help me out here!!

thanks

Hi Gord,

Thanks..i just figured out too and run in the solution..thanks again for
your support.



"Gord Dibben" wrote:

Oh crap............slaps forehead and starts again<g

Sorry for the problems I have caused you with my inattention.

I gave you the the wrong function.

DataValidationCustom

Enter =COUNTIF($A1:$C1,"J")=1


Gord

On Thu, 9 Jul 2009 22:53:01 -0700, Peter Jonkers
wrote:

Thanks again ,

I tried this but i am still able to fill te cells with "j".
I did exactly what you did..is this maybe a excel 2007 issue?

cheers

"Gord Dibben" wrote:

You don't have to be an Excel guru to do this.

Select A1:C40 and enter this formula in FormatConditional
FormattingFormula is:

=COUNTIF($A1:$C1,"J")=1

Note the change in $ signs which makes the row reference relative.

If any of the cells in each row of columns A:C has a "J" then users will be
prevented from entering another "J" in that row.


Gord


On Thu, 9 Jul 2009 08:13:02 -0700, Peter Jonkers
wrote:

thanks for the quick response.
I

The thing is .. I am not an Excel "gooroe".
How can u use this formula and if the condition is true...,how can i
generate a popup for the user

and another question is..i have about 40 rows that i have to use this code
for..how do I use it then

thanks again


cheers

Peter

"Gord Dibben" wrote:

Conditional FormattingFormula is:

=COUNTIF($A$1:$C$1,"J")=1

Edit the error message to suit.


Gord Dibben MS Excel MVP

On Thu, 9 Jul 2009 06:36:01 -0700, Peter Jonkers
wrote:

I have 3 cells called "overtime" Traveltime"and "Servicetime"
Those cells are in a single row.
I am looking for a way so that only ONE of those cells are allowed to be
filled with the letter "J". So only one cell i allowed. When already a cell
is filled, the a message must be poped up like "already one value entered,
only one value is allowed".

I hope somebody can help me out here!!

thanks