search

OfficeDev / office-js

A repo and NPM package for Office.js, corresponding to a copy of what gets published to the official "evergreen" Office.js CDN, at https://appsforoffice.microsoft.com/lib/1/hosted/office.js.
https://learn.microsoft.com/javascript/api/overview
Other
687 stars 94 forks source link

[Excel] Data validation list with indirect reference not being applied #873

Closed jagibe closed 4 years ago

jagibe commented 4 years ago

We're trying to add a data validation list, that references a column table. In order to automatically add the option if new row is added, we use the INDIRECT formula.

Expected Behavior

The data validation list is added to the cell.

Current Behavior

Doing this validation manually, works perfect. From code the list icon appears, but the list is not shown when clicked. When the data validation dialog is opened, the formula is shown correctly. Clicking the Accept button (without any change) applies the validation correctly.

Steps to Reproduce

...
context.workbook.worksheets.getItem("Sheet1").getRange("A1").dataValidation.rule = {
    list: {
        inCellDropDown: true,
        source: "=INDIRECT(\"Table1[Column1]\")"
    }
};
...

Environment

weshi commented 4 years ago

@jagibe I tried both on Excel for the Web and Excel for Windows, but I didn't reproduce this issue. They work as expected, i.e., once you set the data validation to the list and linked to the table, then whenever you add a new row to the table, it is automatically reflected in the validation list.

jagibe commented 4 years ago

@weshi Hi. Thanks for your fast reply. We were 2 days trying to find the problem. We were using the local formula ("=INDIRECTO") instead of the english version ("=INDIRECT")

Sorry and thank you for your time and help