Conditional Data Validation using named range and indirect

[recon2710]

I am trying to make conditional list valdation based on another data validation list. The way to do this in excel is define a named range and then the dependant select list in the source use "indirect()" function to refer to the initial list. The problem I have is setting indirect in th data validation I use "SetDropList()" which takes a []string; I get around this by just having one item in the slice "indirect()" and then passing.

Everything looks fine however the validation does not work until I manually click in to it a press OK; then everything works fine. I think the issue is the "SetDropList()" passes indirect() as a string and it doesnt recognize it as a formula until I click ok.

any suggestion on how to pass formula into data validation source? Maybe another method?

BTW bought you coffee the other day. Great Package!!

[xuri]

Thanks for your issue and donation, I've added you to the thanks page. Now SetRange accepts int, float64, or string data type formula argument, you can specify the formula in the data validation range, for example:

package main

import (
    "fmt"

    "github.com/xuri/excelize/v2"
)

func main() {
    f := excelize.NewFile()
    if err := f.SetSheetRow("Sheet1", "A2", &[]interface{}{"B2", 1}); err != nil {
        fmt.Println(err)
        return
    }
    if err := f.SetSheetRow("Sheet1", "A3", &[]interface{}{"B3", 3}); err != nil {
        fmt.Println(err)
        return
    }
    dvRange := excelize.NewDataValidation(true)
    dvRange.Sqref = "A1:B1"
    if err := dvRange.SetRange("INDIRECT($A$2)", "INDIRECT($A$3)", excelize.DataValidationTypeWhole, excelize.DataValidationOperatorBetween); err != nil {
        fmt.Println(err)
        return
    }
    dvRange.SetError(excelize.DataValidationErrorStyleStop, "error title", "error body")
    if err := f.AddDataValidation("Sheet1", dvRange); err != nil {
        fmt.Println(err)
        return
    }
    if err := f.SaveAs("Book1.xlsx"); err != nil {
        fmt.Println(err)
    }
}

[recon2710]

So this fixed it, but the List in validation only has one line in excel. So second formula is no need as well as second data type. Also, there is no method for list so used numerical value. this is my usage.

dvRange.SetRange("INDIRECT($A$6)", "", 5, excelize.DataValidationOperatorBetween);

[xuri]

If you wanna set the drop list in set data validation, please using the dvRange.SetDropList instead of dvRange.SetRange.

[recon2710]

I have tried this, the goal for me was to place the formula "INDIRECT($A$6)" in the drop list validation, I tried SetDroplist (which only takes a slice BTW), but as stated earlier dvRange.SetDropList causes excel to treat "INDIRECT($A$6)" as a string rather than formula. Excel does not recognize the ""INDIRECT($A$6)" as a formula with setdroplist until i manually go into the validation in excel press ok.

anyway Im ok, using SetRange as is. Thank you for your attention, really appreciate it

[xuri]

You're welcome, please try to add an equal symbol before formula string like this:

dvRange.SetDropList([]string{"=INDIRECT($A$6)"})

[recon2710]

Does not work. List shows "=INDIRECT($A$6)" as only option until I go data>datavalidation and click ok.