如何跨多个Google Sheets工作表生成已勾选复选框项目列表

Hey there! I’ve dealt with exactly this kind of multi-tab checkbox tracking in Google Sheets before, so let’s get you sorted out. The issue with your earlier VLOOKUP/IF-MATCH attempts is that those functions work best with single, defined datasets—they don’t easily traverse multiple tabs to aggregate matching criteria. Here are two reliable solutions:

Option 1: Use QUERY to Combine & Filter Data #

This is my go-to for clean, readable aggregation. It first merges data from all your tabs, then filters for only checked items and returns their locations.

Assuming:

• Checkboxes are in column A of each tab
• "Item Location" is in column B of each tab
• Your tabs are named Sheet1, Sheet2, Sheet3 (adjust these to match your actual tab names)

Use this formula in your summary tab:

=QUERY(
  {Sheet1!A:B; Sheet2!A:B; Sheet3!A:B},
  "SELECT Col2 WHERE Col1 = TRUE AND Col2 IS NOT NULL",
  0
)

Breakdown: #

• The curly braces {...} combine the A-B ranges from each tab into a single virtual dataset. Add ; SheetN!A:B for every additional tab you need to include.
• QUERY then selects column 2 (your Item Locations) where column 1 (checkboxes) is checked (TRUE) and the location isn’t blank.
• The final 0 tells QUERY there’s no header row in the combined data (change to 1 if your tabs have headers).

Option 2: Use FILTER + FLATTEN for Direct Filtering #

If you prefer a more straightforward filtering approach, this combo works great too:

=FILTER(
  FLATTEN({Sheet1!B:B; Sheet2!B:B; Sheet3!B:B}),
  FLATTEN({Sheet1!A:A; Sheet2!A:A; Sheet3!A:A})=TRUE,
  FLATTEN({Sheet1!B:B; Sheet2!B:B; Sheet3!B:B})<>""
)

Breakdown: #

• FLATTEN converts the multi-tab location columns into a single vertical list, and does the same for the checkbox columns.
• FILTER keeps only the locations where the corresponding checkbox is checked and the location isn’t empty.

Important Notes: #

• If your tab names have spaces or special characters, wrap them in single quotes: 'Q3 Inventory'!A:B
• If your checkbox/location columns are in different positions (e.g., checkboxes in column C, locations in column E), adjust the ranges in the formulas accordingly.
• For a scalable solution with many tabs, you could use INDIRECT with a list of tab names, but the above options are simpler for most use cases.

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