In the
process of using
data validation,
named ranges and indirect
function in the application, we are actually creating dependendent
lists which make data entry easier and error free.
Let's say we
wish to enter names of employees in an Excel worksheet. Now the
employees belong to specific departments like Admin, RnD and Marketing.
In the normal data entry process we would have to remember the
departments as well as the names of the people in the different
departments.
In our data validation process we create a list of departments and give
the list a name, let's say, 'department'. Now using each of the
department names we create headers and also create a list of the names
of the employees under this department. Now we select the names of the
employees, let's say, in the Admin department and assign a named range
'admin'. In this manner we create named ranges for all the relevat
departments.
Now
we create a header called department in our worksheet. We can then
select one cell or many cells below the 'department' header, click on
data and then select data validation. In the data validation window we
select under 'allow' the option 'list' and under source we write the
named range 'department'.