Set-SLDataValidation


Synopsis

Add Datavalidation.

Syntax

Description

Create drop-down lists or otherwise control the type of data that users enter on a worksheet.
Apply data constraints on Integers,decimals,Date,Time,TextLength or custom forumulas.

Parameters

-WorkBookInstance

Instance of an excel document that can be used for editing.

Required? True
Position? 0
Default value  
Accept pipeline input? true (ByValue)
Accept wildcard characters? False

-WorksheetName

Name of the Worksheet that is to be processed.

Required? True
Position? 1
Default value  
Accept pipeline input? true (ByPropertyName)
Accept wildcard characters? False

-ValidationTarget

The target cell or range of cells that need to have datavalidation.

Required? True
Position? 2
Default value  
Accept pipeline input? true (ByPropertyName)
Accept wildcard characters? False

-DataLookupRange

The range that can be used to create a drop-down list on a cell or range of cells.

Required? True
Position? named
Default value  
Accept pipeline input? true (ByPropertyName)
Accept wildcard characters? False

-DefinedName

The definedname that can be used to create a drop-down list on a cell or range of cells.

Required? True
Position? named
Default value  
Accept pipeline input? true (ByPropertyName)
Accept wildcard characters? False

-Decimal

Restrict data entry to a decimal number Example – “1.3”.

Required? True
Position? named
Default value  
Accept pipeline input? true (ByPropertyName)
Accept wildcard characters? False

-StartDecimal

This is the minimum value for a decimal range.

Required? True
Position? named
Default value  
Accept pipeline input? true (ByPropertyName)
Accept wildcard characters? False

-EndDecimal

This is the maximum value for a decimal range.

Required? True
Position? named
Default value  
Accept pipeline input? true (ByPropertyName)
Accept wildcard characters? False

-ValidationOperator

The Operator to be used for validating data.
Use tab or intellisense to select from a list of possible values.
&;39;Equal&;39;,&;39;NotEqual&;39;,&;39;GreaterThan&;39;,&;39;LessThan&;39;,&;39;GreaterThanOrEqual&;39;,&;39;LessThanOrEqual&;39;,&;39;Between&;39;

Required? False
Position? named
Default value  
Accept pipeline input? true (ByPropertyName)
Accept wildcard characters? False

-WholeNumber

Restrict data entry to a wholenumber Example – “3”.

Required? True
Position? named
Default value  
Accept pipeline input? true (ByPropertyName)
Accept wildcard characters? False

-StartWholeNumber

This is the minimum value for a wholenumber range.

Required? True
Position? named
Default value  
Accept pipeline input? true (ByPropertyName)
Accept wildcard characters? False

-EndWholeNumber

This is the maximum value for a wholenumber range.

Required? True
Position? named
Default value  
Accept pipeline input? true (ByPropertyName)
Accept wildcard characters? False

-Date

Restrict data entry to a Date Example – “12/25/2014″.

Required? True
Position? named
Default value  
Accept pipeline input? true (ByPropertyName)
Accept wildcard characters? False

-StartDate

This is the minimum value for a Date range.

Required? True
Position? named
Default value  
Accept pipeline input? true (ByPropertyName)
Accept wildcard characters? False

-EndDate

This is the maximum value for a Date range.

Required? True
Position? named
Default value  
Accept pipeline input? true (ByPropertyName)
Accept wildcard characters? False

-Time

Restrict data entry to a Time Example – “14:30:55″.

Required? True
Position? named
Default value  
Accept pipeline input? true (ByPropertyName)
Accept wildcard characters? False

-StartTime

This is the minimum value for a Time range.

Required? True
Position? named
Default value  
Accept pipeline input? true (ByPropertyName)
Accept wildcard characters? False

-EndTime

This is the maximum value for a Time range.

Required? True
Position? named
Default value  
Accept pipeline input? true (ByPropertyName)
Accept wildcard characters? False

-TextLength

Restrict data entry to a TextLength Example – “6”.

Required? True
Position? named
Default value  
Accept pipeline input? true (ByPropertyName)
Accept wildcard characters? False

-StartTextLength

This is the minimum value for a TextLength range.

Required? True
Position? named
Default value  
Accept pipeline input? true (ByPropertyName)
Accept wildcard characters? False

-EndTextLength

This is the maximum value for a TextLength range.

Required? True
Position? named
Default value  
Accept pipeline input? true (ByPropertyName)
Accept wildcard characters? False

-CustomFormula

Restrict data entry to values that conform to a CustomFormula Example – “=len(b3)”.

Required? True
Position? named
Default value  
Accept pipeline input? true (ByPropertyName)
Accept wildcard characters? False

This cmdlet supports the common parameters: Verbose, Debug,
ErrorAction, ErrorVariable, WarningAction, WarningVariable,
OutBuffer, PipelineVariable, and OutVariable. For more information, see
about_CommonParameters (http://go.microsoft.com/fwlink/?LinkID=113216).

Inputs

String,Int,Double,SpreadsheetLight.SLDocument

Outputs

SpreadsheetLight.SLDocument

Notes

Examples

Example 1

Set column vlaues B3 to B7 and use that range to create a drop-down list in cell C3.

Example 2

Use a predefined range B3:B7(values we created in example 1 above) to create a drop-down list in a range of cells D3:E4.

Example 3

Set column vlaues C3 to C7 and use that range to create a DefinedName called &;39;lookuprange1&;39; and finally use the DefinedName to create a drop-down list in cell F3.

Example 4

Set datavalidation on cell c4 to contain only values that are between 1.2 and 2.5.
Note: if you omit the ValidationOperator in the command above the validation operator defaults to &;39;notbetween&;39;
so in effect the validation would then be all values that are not between 1.2 and 2.5

Example 5

Set datavalidation on cell c5 to contain only value that is equal to 5.

Example 6

Set column values D3:D5 and also E3:E5.
Create defined names for each of the above ranges
Set datavalidation on C13 that makes use of the 2 defined names created above.

Example 7

Restrict cell c14 to contain dates between &;39;12/20/2014&;39; & &;39;12/25/2014&;39;

Example 8

Restrict cell c17 to contain date equal to &;39;12/25/2014&;39;.

Example 9

Restrict cell c20 to contain time values that are lessthan 14:20:35.

Example 10

The forumala SUM(LEN(B3),LEN(F3)) –> compute the length of the cell value B3, compute the length of cell value F3 and add them up.
Restrict cell c23 to contain time values that are lessthanoeEqual to the textlength obtained by the formula above.
If the forumal SUM(LEN(B3),LEN(F3)) yeilded value 11 then the total length of the value in cell C23 cannot exceed 11.

Example 11

The forumala &;39;COUNTIF($D$14:$D$17,D14) <= 1&;39; --> count the occurrences of the value in cell D14, in the range $D$14:$D$17. The formula&;39;s result must be 1 or 0
The net result is to prevent duplicate values from being entered in the range D14:D17.

Related links

Stay in touch

Linkedin // RSS Feed