Validating data in excel

25-Jan-2017 19:36

In the course of showing her, I realized that, therein, was one of those handy little tips worth sharing.

In many server environments, Microsoft Office is not installed.

As a SQL Server Business Intelligence Professional, I work with numerous large Excel files every day.

I need to either submit files to others or upload the files to my SQL Server data warehousing system.

I have a standard way of generating dropdowns that gets around the limitation described earlier: rather than entering the list of values directly in the data validation dialog box, I reference a named range.

Using the same household chores scenario, I would accomplish the same end result, update the cell values that have already been populated — just the available values in the dropdown anywhere that named range is used.

In many server environments, Microsoft Office is not installed.

As a SQL Server Business Intelligence Professional, I work with numerous large Excel files every day.

I need to either submit files to others or upload the files to my SQL Server data warehousing system.

I have a standard way of generating dropdowns that gets around the limitation described earlier: rather than entering the list of values directly in the data validation dialog box, I reference a named range.

Using the same household chores scenario, I would accomplish the same end result, update the cell values that have already been populated — just the available values in the dropdown anywhere that named range is used.

Copied data is not validated, nor is the data input in the cell before making the rule.