Category: DATA VALIDATION

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

{ 4 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 […]

Read More →

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 […]

Read More →

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 […]

Read More →

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 […]

Read More →

Automatically change validated entries when source of validation list changes

{ 90 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 […]

Read More →

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 […]

Read More →

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 […]

Read More →