Welcome to Software Development on Codidact!
Will you help us build our independent community of developers helping developers? We're small and trying to grow. We welcome questions about all aspects of software development, from design to code to QA and more. Got questions? Got answers? Got code you'd like someone to review? Please join us.
Post History
On top of values seemingly coming from nowhere in multiple columns, they also affect each other's behavior: In the GIF below, if the cells in the left column have no value selected, then there is n...
#2: Post edited
by
toraritte
·
2023-11-21T16:27:57Z (about 1 year ago)
- On top of values seemingly coming from nowhere in multiple columns, they also affect each other's behavior: In the GIF below, if the cells in the left column have no value selected, then there is no dropdown in the cells in the right column **and** they also don't accept arbitrary values (as a validation warning will pop up).
- [![enter image description here][1]][1]
- I know of the basic data validation drop-down methods:
- 1. Select a range of cells
- 2. Go to `Data` > `Data Validation`
- 3. Select "List" in `Allow`
- 4. At `Source`, either specify the values or select a range of cells that hold them
- [![enter image description here][2]][2]
- For the columns in the GIF, `Source` shows stuff that's new to me:
- + for the **left** column: `=Status`
- + for the **right** column: `=INDIRECT(V7)` (the cell reference increments with every row)
- There is no `=Status` Excel formula, I checked in the [official list][3]. I did find the [`INDIRECT` function][4], but the doc page doesn't make much sense in my case, even after looking at the examples.
I even listed all Data Validation rules programmatically using [NPOI][5], but didn't found extra ones, and the ones defined on the two columns showed the same values as in the Excel app.- [1]: https://i.stack.imgur.com/DewXr.gif
- [2]: https://i.stack.imgur.com/mzs4v.png
- [3]: https://support.microsoft.com/en-us/office/excel-functions-alphabetical-b3944572-255d-4efb-bb96-c6d90033e188
- [4]: https://support.microsoft.com/en-us/office/indirect-function-474b3a3a-8a26-4f44-b491-92b6306fa261
- [5]: https://github.com/nissl-lab/npoi
- On top of values seemingly coming from nowhere in multiple columns, they also affect each other's behavior: In the GIF below, if the cells in the left column have no value selected, then there is no dropdown in the cells in the right column **and** they also don't accept arbitrary values (as a validation warning will pop up).
- [![enter image description here][1]][1]
- I know of the basic data validation drop-down methods:
- 1. Select a range of cells
- 2. Go to `Data` > `Data Validation`
- 3. Select "List" in `Allow`
- 4. At `Source`, either specify the values or select a range of cells that hold them
- [![enter image description here][2]][2]
- For the columns in the GIF, `Source` shows stuff that's new to me:
- + for the **left** column: `=Status`
- + for the **right** column: `=INDIRECT(V7)` (the cell reference increments with every row)
- There is no `=Status` Excel formula, I checked in the [official list][3]. I did find the [`INDIRECT` function][4], but the doc page doesn't make much sense in my case, even after looking at the examples.
- I even listed all Data Validation rules programmatically using [NPOI][5], but didn't find extra ones, and the ones defined on the two columns showed the same values as in the Excel app.
- [1]: https://i.stack.imgur.com/DewXr.gif
- [2]: https://i.stack.imgur.com/mzs4v.png
- [3]: https://support.microsoft.com/en-us/office/excel-functions-alphabetical-b3944572-255d-4efb-bb96-c6d90033e188
- [4]: https://support.microsoft.com/en-us/office/indirect-function-474b3a3a-8a26-4f44-b491-92b6306fa261
- [5]: https://github.com/nissl-lab/npoi
#1: Initial revision
by
toraritte
·
2023-11-21T16:24:35Z (about 1 year ago)
Drop-down values in Excel cells are not specified in Data Validation rules and global search didn't find them in workbook, so where do they come from?
On top of values seemingly coming from nowhere in multiple columns, they also affect each other's behavior: In the GIF below, if the cells in the left column have no value selected, then there is no dropdown in the cells in the right column **and** they also don't accept arbitrary values (as a validation warning will pop up). [![enter image description here][1]][1] I know of the basic data validation drop-down methods: 1. Select a range of cells 2. Go to `Data` > `Data Validation` 3. Select "List" in `Allow` 4. At `Source`, either specify the values or select a range of cells that hold them [![enter image description here][2]][2] For the columns in the GIF, `Source` shows stuff that's new to me: + for the **left** column: `=Status` + for the **right** column: `=INDIRECT(V7)` (the cell reference increments with every row) There is no `=Status` Excel formula, I checked in the [official list][3]. I did find the [`INDIRECT` function][4], but the doc page doesn't make much sense in my case, even after looking at the examples. I even listed all Data Validation rules programmatically using [NPOI][5], but didn't found extra ones, and the ones defined on the two columns showed the same values as in the Excel app. [1]: https://i.stack.imgur.com/DewXr.gif [2]: https://i.stack.imgur.com/mzs4v.png [3]: https://support.microsoft.com/en-us/office/excel-functions-alphabetical-b3944572-255d-4efb-bb96-c6d90033e188 [4]: https://support.microsoft.com/en-us/office/indirect-function-474b3a3a-8a26-4f44-b491-92b6306fa261 [5]: https://github.com/nissl-lab/npoi