Using Multiple Functions Excel

Data validation, Named Ranges and Indirect Function in Excel 2007

In the process of using data validation, named ranges and indirect function in the application, we are actually creating dependent 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’.

Next to the ‘department’ header we write ‘Name’ as the header. We select a single cell or many cells below this header, click on data, data validation and in the data validation window we select under allow ‘list’ and in the source we type ‘=indirect(cell address below the department header)’. Now we click OK.
If no department is selcted an error message appears. Just click ok. Next select a department, let’s say, ‘admin’ below the department header after selecting it from the drop down options. Now select the cell below the name header, a drop down option arrow appears, click on it and select the name you wish to enter. Doesn’t the process make data entry more convenient and error free?

Further reading:
Excel INDIRECT Function
Indirect Function in MS Excel

Leave a Reply

Your email address will not be published. Required fields are marked *