Up next in 10
Conditional Data Validation: Restrict Data Entry Based on Other Cell Values In Excel
3K views
Nov 7, 2024
In this Excel tutorial, discover how to prevent data entry in one cell range based on the values in another cell range. Enhance your data validation skills by learning to create CUSTOM RULES that enforce data consistency and accuracy.
View Video Transcript
0:00
hello everyone in today's tutorial we'll
0:02
learn how to prevent data entry into a
0:04
cell range based on the value of other
0:06
cells in Excel let's consider a scenario
0:08
where we have two columns name and age
0:11
we want to prevent data entry in the age
0:13
column if the name column is empty
0:15
select the range of cells in the age
0:16
column where you want to apply the data
0:18
validation click on the data tab in the
0:20
ribbon and then click on data validation
0:22
in the data tools group in the data
0:24
validation dialogue box go to the
0:26
settings tab in the allow dropdown menu
0:29
select custom
0:30
in the formula field enter formula as
0:32
shown on the screen this formula checks
0:34
if the corresponding cell in name column
0:36
is not empty if it is the formula
0:39
returns false preventing data entry in
0:41
the age cell otherwise it returns true
0:44
allowing data entry now when you attempt
0:46
to enter a value into a cell in the age
0:48
column without entering the value into
0:50
the cell in the name column the
0:52
validation rule is triggered and the
0:53
error alert is
0:58
shown the current formula effectively
1:00
validates entries only when the
1:02
corresponding cell in the name column is
1:04
not empty but does not restrict users
1:06
from entering non-numeric values in the
1:07
age column to enhance it we will modify
1:10
the formula to ensure that only numeric
1:12
values are accepted in the age column
1:14
when the corresponding cells in the name
1:15
column are populated the updated formula
1:18
will verify if the cells in the age
1:19
column contain numeric values and
1:21
confirm that the corresponding cells in
1:23
the name column are not empty after
1:25
updating the formula it effectively
1:27
prevents users from entering non-numeric
1:29
values in in the age column when the
1:31
corresponding name column is
1:35
empty next if you want to provide a
1:38
message to guide users select the age
1:40
column again and open the data
1:41
validation dialogue box go to the input
1:44
message tab enter a title and input
1:46
message such as enter age and numbers
1:48
only go to the error alert tab to
1:51
customize the error message users will
1:52
see if they try to enter data when it's
1:54
not allowed enter a title and error
1:56
message click okay to apply the data
1:59
validation rule
2:00
now try entering age where the name cell
2:02
is empty Excel should prevent you from
2:04
entering data and display the custom
2:06
error message thanks for watching if you
2:10
found this tutorial helpful please give
2:12
it a thumbs up share it with your
2:13
friends and subscribe to our channel for
2:15
more Excel tips and tutorials if you
2:17
have any questions or suggestions leave
2:19
them in the comments below see you in
2:21
the next video