Check 3 cells, only one of the cells my be filled

[Peter Jonkers]

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

 

[Gord Dibben]

Conditional Formatting>Formula is:

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

Edit the error message to suit.


Gord Dibben MS Excel MVP

 

[Peter Jonkers]

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

 

[Peter Jonkers]

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

 

[Gord Dibben]

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

Select A1:C40 and enter this formula in Format>Conditional
Formatting>Formula 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

 

[Peter Jonkers]

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]

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.

Data>Validation>Custom

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


Gord

 

[Peter Jonkers]

Hi Gord,

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