# Preventing Data Entry if another cell has data already entered

#### Rodster1966

##### New Member
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.

Kind Regards

Rod
(Rodster1966)

### Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},\$Z\$1:\$Z\$99,\$Y\$1:\$Y\$99),2,False) to lookup Y values to left of Z values.

#### Tetra201

##### MrExcel MVP
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

##### New Member
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

##### MrExcel MVP
... 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

##### Board Regular
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

##### New Member
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

##### MrExcel MVP
... 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.

Replies
3
Views
791
Replies
6
Views
507
Replies
2
Views
995
Replies
17
Views
2K
Replies
12
Views
923

1,191,580
Messages
5,987,446
Members
440,097
Latest member
Wint

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back