Hey Buckley, great question! I’ve tackled similar dynamic calendar/table projects in Google Sheets before, and we can absolutely build this fully dynamic solution without any scripting—just using advanced array formulas and Google Sheets’ built-in functions. Let’s break this down step by step.

第一步：优化中间数据提取公式 #

Your current formula to split plant names and map them to week numbers works, but we can streamline it for better readability and performance. Replace your existing formula with this:

=SORT(UNIQUE(ARRAYFORMULA(SPLIT(FLATTEN(FILTER(Sheet1!B2:B&"•"&ISOWEEKNUM(Sheet1!A2:A), LEN(Sheet1!B2:B))), "•"))), 1, TRUE, 2, TRUE)

• FILTER keeps only rows with non-empty plant lists
• FLATTEN expands the comma-separated plant names into individual rows paired with their corresponding week number
• SPLIT separates the plant name and week number using the • delimiter
• UNIQUE removes duplicate plant-week combinations
• SORT organizes results alphabetically by plant, then numerically by week

Name this sheet Data (you can keep your existing sheet name, but I’ll use Data for clarity in the next steps).

第二步：构建动态开花周历表格 #

Now we’ll create a fully dynamic table where:

• Rows automatically update with new plant species
• Columns automatically update with new week numbers
• Checkboxes toggle automatically based on your collected data

1. Create a new sheet named BloomCalendar
2. Paste this formula into cell A1 (this will generate the entire table, including headers and checkbox-ready values):

=LET(
  // Get sorted unique list of plants
  plants, SORT(UNIQUE(QUERY(Data!A:A, "select A where A is not null")), 1, TRUE),
  // Get sorted unique list of weeks (1-53)
  weeks, SORT(UNIQUE(QUERY(Data!B:B, "select B where B is not null")), 1, TRUE),
  // Create a matrix where each cell is TRUE if the plant blooms that week, FALSE otherwise
  bloom_matrix, ARRAYFORMULA(COUNTIFS(Data!A:A, plants, Data!B:B, TRANSPOSE(weeks)) > 0),
  // Combine headers and matrix into a single table
  {{"Plant", weeks}; {plants, bloom_matrix}}
)

第三步：转换为复选框可视化 #

The formula above outputs TRUE/FALSE values—let’s turn these into checkboxes for better readability:

1. Select the entire data range in BloomCalendar (from A1 to the last populated cell)
2. Go to Data > Data validation
3. Under "Criteria", select Checkbox
4. Check the box for "Use cell value to determine state"
5. Set "Checked value" to TRUE and "Unchecked value" to FALSE
6. Click Save

How this works #

• Dynamic rows/columns: UNIQUE and QUERY automatically pull in new plants or weeks as your form collects more data—no need to manually add rows/columns
• Boolean matrix: COUNTIFS checks if each plant-week combination exists in your Data sheet; if it does, it returns TRUE (checkbox checked), otherwise FALSE (unchecked)
• LET function: Makes the formula clean and easy to modify—you can adjust sorting order or filters by editing the plants/weeks variables

Bonus: Handle edge cases #

If you want to ensure weeks are always shown as 1-53 (even if no data exists for a week), you can replace the weeks variable in the formula with:

SEQUENCE(53, 1, 1)

This will force all 53 weeks to appear as columns, even if there’s no data for some of them.

备注：内容来源于stack exchange，提问作者Buckley