Category: DATA VALIDATION

Generate a list of assignees for different projects based on a competency matrix

{0 Comments}

Here is a small sample of a Project matrix which shows tasks to be accomplished for various projects.  There can only be upto 6 tasks per project.

Project Name Task1 Task2 Task3 Task4 Task5 Task6
Project1 Painting Chef Gardener
Project2 Tiling Digging Engineering
Project3 Mechanic Engineering

Here is a competency matrix showing the competencies of employees on different tasks.  1 indicates that the employee is competent to perform that task.

Task Tom Jane Mary Paddy Lynda
Painting 1 1 1 1 1
Tiling 1 1 1 1 1
Plastering 1 1 1 1 1
Digging 1 0 1 1 1
Mechanic 1 1 1 0 1
Detective 1 1 1 1 1
Engineering 1 1 0 1 1
Boxer 1 0 1 1 1
Chef 1 1 1 1 1
Gardener 1 1 0 1 1
Banker 1 1 1 1 0

From these two tables, one may want to generate another table showing which employees can be assigned to which project (only those employees should be assigned to a project who can complete all tasks).  So the ideal solution is to create another column (8th column) in the Project matrix table above which should have a drop down (Data > Data Validation) for every project showing which employees are competent for that project.

Here's an illustration:

Assuming that the Project matrix is in range A1:G4 (headers are in row 1)

  1. In cell H2 (for Project1), the drop down should show Jane, Lynda, Paddy and Tom.  Mary should not appear there because she cannot perform one of the 3 tasks required to complete the project i.e. Gardener.
  2. In cell H3 (for Project2), the drop down should show Lynda, Paddy and Tom.  Jane and Mary should not appear there because they cannot perform the Digging and Engineering tasks respectively.

The solution is dynamic for the following:

  1. Projects added to the Project matrix Table; and
  2. Tasks added (upto 6 only) or edited in the Project matric Table; and
  3. Employees added to the Competency matrix Table; and
  4. Tasks added to the Competency matrix Table

I have solved this problem by using:

  1. Power Query; and
  2. Formulas in Data > Data Validation.

You may download my solution workbook from here or here.

Removing dependent validation list from cell for one case

{4 Comments}

In cell B6, a user can select one of the following entries from a validation drop down list - Reason1, Reason2 and Reason3.  If the user selects either Reason1 or Reason2, a dependent validation drop down lists should appear in cell C6.  However, if the user select Reason3 in cell B6, the dependent validation list from cell C6 should vanish thereby allowing the user to enter any value in cell C6.

You may refer to my solution in the this workbook.

Secondary validation cell entry to update when primary validation cell changes

{30 Comments}

Having created primary and secondary validation drop downs, the issue is that when a primary validation cell changes, the value in the secondary validation cell does not become blank (or change automatically to first entry of the secondary validation list).  Instead the secondary validation cell remains the same as what was already selected earlier.

In the this workbook, i present the following three cases of dealing with the secondary validation cell value when the primary validation cell changes:

1. The moment the primary validation entry changes, blank out the secondary validation cell
2. The moment the primary validation entry changes, show the corresponding first entry of the secondary validation drop down
3. The moment the primary validation entry changes, perform the following:
a. If there is only one corresponding entry in the secondary validation drop down, then show that entry (no need to select); and
b. If there are multiple corresponding entries in the secondary validation drop down, then blank out the secondary validation cell

Blanks appearing in source data not to appear in Data validation list

{0 Comments}

Assume a column of data with blanks appearing in a few random cells.  One may want to use this range of cells for creating a Validation drop down in a specified cell.  After creating the validation drop down, the blanks from the source data range would obviously show up in the drop down as well.

The task is to show only the entries in the validation drop down (without the blanks).  The question and instructions are very clearly mentioned in this workbook.

Depending upon the version of MS Excel which you are using, there could be two ways to solve this problem

Solution for MS Excel 2010 and higher versions

If you are using the Power Query add-in, then a few simple steps (no formulas at all) can solve this problem.  The result will be dynamic and refreshable (just as in a Pivot Table)

You may watch a short video of my solution here

Solution for all versions of MS Excel

While this solution works for all versions of MS Excel, it uses an array formula (Ctrl+Shift+Enter).  Array formulas, if used extensively in the workbook, adversely effect the system's performance.

Automatically change validated entries when source of validation list changes

{82 Comments}

After creating a validation list in a cell, one may enter one any value in that cell from the cell drop down.  Once a selection is made in the cell drop down, a change in existing source entries (say a spelling change) of the validation list will not cause the cell entry to be changed (although the cell drop down values change).

To change the cell value when the validation source entries change, refer to the this workbook.

Reduce scrolling in data validation

{4 Comments}

Given a large number of entries in the source of data validation, it may become cumbersome to scroll down the list to select a desired value.  It would be ideal to have the user type the first alphabet and be taken to all words which start with that alphabet.

The drop down list in cell C2 is the normal drop down list created through data validation.  This list does not have your desired functionality.  However, the drop down list in cell E2 has the desired functionality.  In cell E2, type the first letter of the any alphabet and then click the drop down button in the cell.  You will be taken to the first alphabet int he list.

You may refer to my solution in this workbook.

Conditional data validation

{14 Comments}

Assume that in range A2:A11, cells have been validated to allow a person to enter Y or N.  In B2:B11, cells should be validated based on the entry selected by the end-user in range A2:A11.  So, if a user selected Y in cell A2, then a drop down should appear in cell B2, allowing him to choose from Delhi, Mumbai, Chennai and Kolkata.  If the user selects N in cell A2, he should be able to enter any value in cell B2.

To accomplish this, try the following procedure

1. Type Delhi, Mumbai, Chennai and Kolkata in I2:I5 and assign it a name (Ctrl+F3), say source1

2. In A2, select Y

3. In cell B2, go to Data > Validation > Allow > List > Source > =IF(A2="Y",source1,B2)

With Y in cell A2, B2 will show four cities in the validation drop down.  When the value in cell A2 changes to N, the user will be able to enter any value in cell B2.