Here's how to create a data validation list based on dynamic cell values in Excel:

Method 1: Using INDIRECT and OFFSET functions (For Excel 2016 and earlier)

1. Create your dynamic list: In a separate area of your worksheet, build the list of items that will populate the dropdown list. This list can be updated based on other cell values, formulas, or user input.

2. Select the cell(s) for the dropdown: Choose the cell(s) where you want the dropdown list to appear.

3. Go to Data Validation: Navigate to the "Data" tab and click "Data Validation" in the "Data Tools" group.

4. Set Allow to List: In the "Settings" tab, under "Allow," choose "List" from the dropdown menu.

5. Define the Source (using INDIRECT and OFFSET):
In the "Source" box, enter the following formula (replace A1:A10 with the actual cell range of your dynamic list and B1 with the cell reference containing the criteria that determines the list):

```excel
=INDIRECT("A"&B1&":A"&OFFSET(B1,COUNTA(A:A)-1,0))
```

Explanation of the formula:
- `INDIRECT`: This function converts a text string into a cell reference.
- `A` & `B1`: This creates the starting cell reference based on the criteria cell (B1).
- `OFFSET(B1,COUNTA(A:A)-1,0)`: This calculates the ending cell reference dynamically.
- `COUNTA(A:A)`: Counts the number of non-blank cells in column A (assuming your list is in A). This determines the length of the list.
- `OFFSET(B1,-(COUNTA(A:A)-1),0)`: Offsets the starting cell reference (B1) down by the list length minus 1 to create the ending reference.

6. Complete the steps (Optional): Follow steps 6 and 7 from the previous explanation on dropdown lists for any additional options like "Ignore blank" or "In-cell dropdown."

7. Click OK: Click "OK" to close the Data Validation window.

Method 2: Using Spill Range with UNIQUE function (For Excel 365 and later):

1. Create your dynamic list: Similar to method 1, build your list in a separate area.

2. Select the cell(s) for the dropdown: Choose the cell(s) where you want the dropdown list to appear.

3. Go to Data Validation: Navigate to the "Data" tab and click "Data Validation" in the "Data Tools" group.

4. Set Allow to List: In the "Settings" tab, under "Allow," choose "List" from the dropdown menu.

5. Define the Source (using Spill Range with UNIQUE):
In the "Source" box, enter the following formula (replace A1:A10 with the actual cell range of your list):

```excel
=UNIQUE(A1:A10#)
```

Explanation of the formula:
- `UNIQUE`: This function removes duplicate entries from a range.
- `A1:A10`: This is the range of your dynamic list.
- `#`: This is the spill range operator (only available in Excel 365 and later). It ensures the formula automatically adjusts the reference based on the number of items in the list.

6. Complete the steps (Optional): Follow steps 6 and 7 from the previous explanation on dropdown lists for any additional options like "Ignore blank" or "In-cell dropdown."

7. Click OK: Click "OK" to close the Data Validation window.

With either method, you should now have a dropdown list that dynamically updates based on the values in your designated cell.