Excel Data List Validation in Powershell

Question

Hi,

I have a range named "STATE". I want to set data validation in range ("A1") to only take value within this range using Powershell.

Below is what I have tried. Does not work. I dont know what to put as 4th and 5th parameters. The first 3 are Excel constants equivalent to xlValidateList, xlValidAlertStop and xlBetween respectively.

$ws.Range("A1").Validation.Add(3, 1, 1, "=STATE", 0)

Please help. Thanks.

Answer 1

Check here: http://olddogsblog.spaces.live.com/blog/cns!C2DB05EEFA6C21A1!129.entry

There's an example titled "Add Validation to an Excel Worksheet".

Answer 2

Found the answer after trial and error.

$missing = [system.type]::missing
$ws.Range("A1").Validation.Delete()
$ws.Range("A1").Validation.Add(3, 1, $missing, "=STATE", $missing)

A1 cell will show a dropdown list populated by values within STATE range.

Note: Dont forget to clear any existing validation rule before applying. Use Validation.Delete(). I spent countless hours because of this.