Here's how to create a dropdown list with dynamic content based on another cell in Excel:

1. Prepare your Data:

- Create two separate lists:
- Main List: This will contain all the unique options for your final dropdown list. Place this list anywhere on your sheet.
- Criteria List: This will contain values that will determine which options appear in the dropdown list. Ideally, this list should be next to the cell where you want the dropdown.

2. Create a Named Range (Optional):

- This step is optional but can improve readability and maintainability.
- Select the Main List cells (all the options for the dropdown).
- Go to the Formulas tab and click Define Name in the Defined Names group.
- In the New Name box, enter a clear name for your list (e.g., "FullList").
- Click OK to create the named range.

3. Create the Dropdown Formula:

- Select the cell where you want the dropdown list to appear.
- Go to the Data tab and click Data Validation in the Data Tools group.
- In the Settings tab, under Allow, choose List from the dropdown menu.
- In the Source box, enter the following formula:

```excel
=INDIRECT(C2)&"#" // Replace C2 with the cell reference containing the criteria value
```

Explanation of the Formula:

- `INDIRECT(C2)`: This part references the cell containing the criteria value (replace C2 with your actual cell reference). The `INDIRECT` function takes a cell reference as text and returns the value in that cell.
- `"&"#"`: This appends a hash (#) symbol to the criteria value. The hash trick tells Excel to treat the entire reference as a spill range, ensuring it captures all relevant options even if the list expands or contracts.

4. Link the Criteria:

- Now you need to connect the dropdown options to the criteria value.
- In the Main List, use the `FILTER` function to dynamically filter the options based on the criteria value.

```excel
=FILTER(FullList, FullList=C2) // Replace FullList with your named range (if used) and C2 with your criteria cell reference
```

- This formula will return a filtered list based on the value in cell C2 (or your chosen criteria cell).

5. Complete Data Validation:

- Back in the Data Validation window, with the formula in the Source box, click OK.

Result:

- Now, when you select the dropdown cell, it will display a list of options that dynamically update based on the value in the criteria cell (C2 in this example). As the criteria value changes, the available options in the dropdown will adjust accordingly.