Google Sheets利用INDIRECT函数实现动态跨表数据合并

Great question! Your approach to maintaining a list of sheet names and using that to drive your summary is exactly the right way to avoid tedious manual formula updates—let’s get this working properly.

Why Your Original Formula Didn’t Work #

The problem with your INDIRECT(CONCATENATE(...)) formula is that INDIRECT() can’t parse the full array syntax ({Sheet1!A2:B;Sheet2!A2:B}) when it’s built as a single string. It’s designed to reference one range at a time, not a concatenated array of ranges.

The Working Solution #

Use a combination of ARRAYFORMULA, INDIRECT, FILTER, QUERY, and UNIQUE to dynamically pull and combine data from all sheets listed in your MASTER tab’s A2:A32 range. Here’s the formula:

=UNIQUE(QUERY({ARRAYFORMULA(INDIRECT("'"&FILTER(A2:A32, A2:A32<>"")&"'!A2:B"))}, "select * where Col1 is not null"))

Breakdown of the Formula #

Let’s walk through what each part does:

• FILTER(A2:A32, A2:A32<>""): First, we filter out any blank cells in your sheet name list to avoid trying to reference non-existent sheets.
• INDIRECT("'"&...&"'!A2:B"): Generates a valid range reference for each sheet name (the single quotes handle sheet names with spaces/special characters like Base, Trim & Crown).
• ARRAYFORMULA: Makes INDIRECT process every sheet name in the filtered list at once, returning a stack of ranges.
• {...}: Combines all those individual ranges into one large array.
• QUERY(..., "select * where Col1 is not null"): Removes any empty rows from the combined array (useful if some sheets have no data in A2:B).
• UNIQUE: Finally, removes duplicate rows from the combined data—just like your original formula did.

How to Use It #

1. In your MASTER sheet, keep column A (A2:A32) updated with all the sheet names you want to summarize. Add new sheet names to this list whenever you create a new tab.
2. Paste the formula above into any empty cell in MASTER (e.g., C2). It will automatically pull data from all listed sheets, combine it, and remove duplicates.
3. When you add a new sheet, just type its exact name into the next empty cell in column A—no need to edit the formula at all.

Key Notes #

• Sheet Name Accuracy: Make sure the names in column A match the actual sheet tab names exactly (capitalization, spaces, and special characters matter).
• Handling Empty Sheets: The QUERY part ensures empty rows from sheets with no data don’t clutter your summary.
• Error Prevention: If you accidentally type a sheet name that doesn’t exist, the formula will return a #REF! error—double-check column A if this happens.

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