如何实现基于其他单元格的动态Excel数据验证列表：逗号分隔拆分与数据源动态指定

Got it, let's tackle these two Excel challenges one by one—both are totally doable with the right functions and setup!

1. Split comma-separated text from a cell into a data validation dropdown #

The problem with directly referencing a cell like $A$3 in data validation is that Excel treats the entire string as a single list item. To split it into individual options, we need to break the string into an array of values first:

• For Excel 365/2021 (supports dynamic arrays):

  1. Navigate to the Data tab, click Data Validation, and select List under the Allow dropdown.
  2. In the Source field, enter this formula:

     =TEXTSPLIT($A$3, ", ")
     

     The TEXTSPLIT function splits the text in A3 using ", " (comma + space) as the delimiter, returning each segment as a separate dropdown option. Adjust the delimiter to just "," if your source text uses commas without spaces.

• For older Excel versions (no TEXTSPLIT support):
  Use FILTERXML to parse the string into an array:

  =FILTERXML("<t><s>"&SUBSTITUTE($A$3, ", ", "</s><s>")&"</s></t>", "//s")
  

  This wraps each split segment in XML tags and extracts them as individual list items.

2. Dynamically reference the source cell using B4's value #

To make the source cell configurable (e.g., use A6's data when B4 is set to A6), we’ll combine the split logic with the INDIRECT function—which converts a text string into a valid cell reference.

• Update your data validation formula to:
  For Excel 365/2021:

  =TEXTSPLIT(INDIRECT($B$4), ", ")
  

  For older Excel versions:

  =FILTERXML("<t><s>"&SUBSTITUTE(INDIRECT($B$4), ", ", "</s><s>")&"</s></t>", "//s")
  

  Now, whenever you change the value in B4 to a valid cell reference (like A6, B2, etc.), the dropdown in C4 will automatically pull and split the text from that target cell.

Quick notes:

• Double-check that the delimiter in your formula matches exactly what’s used in your source cell (e.g., "," vs ", ").
• INDIRECT is case-insensitive, so a6 will work the same as A6.
• If the source cell is empty, the dropdown will be blank. Add IFERROR if you want to handle this gracefully, e.g., =IFERROR(TEXTSPLIT(INDIRECT($B$4), ", "), "")

内容的提问来源于stack exchange，提问作者Ben