Post History

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`.

[![enter image description here][1]][1]

After expansion:

[![enter image description here][2]][2]

### 2. LEFT column still: The `=Status` formula in Data Validation rule

Again, [there is no built-in `Status` function in Excel][3], so where does it come from? Decided to enter `=Status` in an empty cell, and several things happened:

1. 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:AV4` got highlighted

2. After pressing enter, the blank cells were replaced with the content of the cells in the referenced range (i.e., `AV3:AV4`).

[![enter image description here][4]][4]

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][5]). 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>)`:

[![enter image description here][6]][6]

I still don't understand what the [`INDIRECT` documentation][8] 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`, 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.)


[its documentation][8] made much more sense now:


  [1]: https://i.stack.imgur.com/PsUTe.png
  [2]: https://i.stack.imgur.com/Pl25V.png
  [3]: https://support.microsoft.com/en-us/office/excel-functions-alphabetical-b3944572-255d-4efb-bb96-c6d90033e188
  [4]: https://i.stack.imgur.com/vcOJ9.gif
  [5]: https://support.microsoft.com/en-us/office/define-and-use-names-in-formulas-4d0f13ac-53b7-422e-afd2-abd7ff379c64
  [6]: https://i.stack.imgur.com/LBQ5o.png
  [7]: https://i.stack.imgur.com/nN8LJ.png
  [8]: https://support.microsoft.com/en-us/office/indirect-function-474b3a3a-8a26-4f44-b491-92b6306fa261