Preventing Data Entry if another cell has data already entered

[Rodster1966]

Hello,

I have 3 cells (G14, J14, N14) - what is the formula i need to put into Data Validation to stop an entry into the other 2 cells


for example, if i enter a figure in G14 I want it to automatically reject any data input into J14 and N14 and so on if data went into one of the other cells.

To further explain. We have a Roster Costing Spreadsheet. We have the option of entering a Salary Figure (G14), Hourly Rate (J14) or Shift Rate (N14). If we enter a Salary figure at G14, we cannot enter an Hourly Rate or Shift Rate. Similarly, if we enter a figure at Hourly Rate, we cannot enter a figure at Salary or Shift. It can only be one or the other.
Many Thanks in advance for your Help.

Kind Regards

Rod
(Rodster1966)

 

[Tetra201]

Try using the following set of rules:

=COUNT($J$14,$N$14)=0 for G14
=COUNT($G$14,$N$14)=0 for J14
=COUNT($G$14,$J$14)=0 for N14

 

[Rodster1966]

Thanks for your reply Tetra but G14, J14 & N14 are Data Entry Cells. Was looking at Data Validation as a means of identifying the incorrect data entry but can't seem to get the formula/specifications correct for each of the three cells.

Cheers

Rod

 

[Tetra201]

... if i enter a figure in G14 I want it to automatically reject any data input into J14 and N14 and so on...

Making my suggestion more verbose.

Try using the following set of rules for Custom Data Validation:

=COUNT($J$14,$N$14)=0 as a Formula for Custom Data Validation of cell G14
=COUNT($G$14,$N$14)=0 as a Formula for Custom Data Validation of cell J14
=COUNT($G$14,$J$14)=0 as a Formula for Custom Data Validation of cell N14

 

[cpatel13]

Making my suggestion more verbose.

Try using the following set of rules for Custom Data Validation:

=COUNT($J$14,$N$14)=0 as a Formula for Custom Data Validation of cell G14
=COUNT($G$14,$N$14)=0 as a Formula for Custom Data Validation of cell J14
=COUNT($G$14,$J$14)=0 as a Formula for Custom Data Validation of cell N14

Hie,
Perhaps Try this,
Put =IF(NOT(ISBLANK($H14)),FALSE) data validation for cells G14
Put =IF(NOT(ISBLANK($G14)),FALSE) data validation for cells H14
Put =IF(NOT(ISBLANK($H14)),FALSE) data validation for cells I14

Regards,
CPatel13


A Like and a feedback would be appreciated

 

[Rodster1966]

Hi Tetra,

Apologies for the late reply but can you please explain how I apply all three of these formulas to the one Custom Data Validation ?

Thanks

Rod

Making my suggestion more verbose.

Try using the following set of rules for Custom Data Validation:

=COUNT($J$14,$N$14)=0 as a Formula for Custom Data Validation of cell G14
=COUNT($G$14,$N$14)=0 as a Formula for Custom Data Validation of cell J14
=COUNT($G$14,$J$14)=0 as a Formula for Custom Data Validation of cell N14

 

[Tetra201]

... Apologies for the late reply but can you please explain how I apply all three of these formulas to the one Custom Data Validation ?

Here is how:

1. Copy the rule =COUNT($J$14,$N$14)=0
2. Select cell G14
3. Go to Data --> Data Validation; a dialog will open
4. In the dialog tab 'Settings', select Allow: Custom
5. In the field 'Formula:', paste the rule
6. Click 'OK'

Repeat steps 1-6 for the other two rules and their respective cells.