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.
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).
I know of the basic data validation drop-down methods:
-
Select a range of cells
-
Go to
Data>Data Validation -
Select "List" in
Allow -
At
Source, either specify the values or select a range of cells that hold them
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. I did find the INDIRECT function, 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, but didn't find extra ones, and the ones defined on the two columns showed the same values as in the Excel app.
1 answer
It took a day and a half, but found it:
1. LEFT column's valid values, Assessed and Pending
Started with the left column, because if it is not set, then the right column is "inactive", so it clearly has to trigger something. After several global workbook searches on the string Pending, I noticed that the cursor jumps to a place where there is nothing. It looked like a glitch, but once the search came full circle, the screen kept jumping to this empty place. Then I realized there are columns "missing": AV, AW, and AX.
After expansion:
2. LEFT column still: The =Status formula in Data Validation rule
Again, there is no built-in Status function in Excel, so where does it come from? Decided to enter =Status in an empty cell, and several things happened:
-
Once the "formula" was entered,
-
a tooltip came up showing
Status PART-IV-SERVICES!AV3:AV4(in generic form:Status <sheet_name>!<cell_range>) -
the cell range
AV3:AV4got highlighted
-
-
After pressing enter, the blank cells were replaced with the content of the cells in the referenced range (i.e.,
AV3:AV4).
After selecting the highlighted range, the "cell selection textbox"(?) in the Formula Bar first showed the cell range, then it switched to Status. As it turns out, cells and cell ranges can be named (see article Define and use names in formulas). So combining =Status and setting List in the Data Validation rule for the column achieves the drop-down effect.
3. RIGHT column: INDIRECT function
The cells in the right column are empty, but their Data Validation rule is set to "List" and the "source" field is set to =INDIRECT(<left_column_cell_ref>):
I still don't understand what the INDIRECT documentation is trying to say, but this is what's happening: When a left column cell is populated with a value (in this case, with either Assessed or Pending), then the INDIRECT(<cell_ref>) becomes either =Pending or =Assessed.
The last piece of the puzzle is that I found two more named ranges, Pending and Assessed (see image below), so when their names are called in a formula, they will behave the same as =Status, but with one level of indirection added to the mix. (Putting it this way, the name of the INDIRECT function does make sense, but I'm still confused how it is supposed to behave.)
0 comment threads
0 comment threads