如何实现将“Active Submittals”工作表中G列非空行的A列数据依次填充至新工作表连续单元格？

Got it, let's fix this so you can automatically pull all matching rows instead of just a single one! Here are two tailored approaches depending on your Excel version:

For Excel 365/2021 (Dynamic Array Enabled) #

This is the simplest, most efficient method using the FILTER function, which automatically spills results into consecutive cells without needing to drag formulas manually.

In your new worksheet's cell A3, enter this formula:

=FILTER('Active Submittals'!A:A, 'Active Submittals'!G:G<>"", "")

• How it works: The FILTER function scans every row in 'Active Submittals'!G:G — any row where G isn't blank will have its corresponding A column value pulled into your new sheet. The third argument "" ensures empty cells appear instead of errors when there are no more matching rows left.
• Pro tip: Make sure there are no existing data below A3 in your new sheet, otherwise you'll get a #SPILL! error (Excel needs clear space to output all results).

For Older Excel Versions (No Dynamic Arrays) #

If you're using an Excel version before 365/2021, you'll need an array formula combination of INDEX, SMALL, and IF to achieve the same result.

In your new worksheet's cell A3, enter this formula, then press Ctrl+Shift+Enter (not just Enter — this tells Excel it's an array formula):

=IFERROR(INDEX('Active Submittals'!$A:$A, SMALL(IF('Active Submittals'!$G:$G<>"", ROW('Active Submittals'!$G:$G)), ROW(A1))), "")

Then drag this formula down from A3 to A4, A5, etc., until you start seeing empty cells (meaning there are no more matching rows left to pull).

Here's a quick breakdown of how the formula works:

• IF('Active Submittals'!$G:$G<>"", ROW('Active Submittals'!$G:$G)): Collects all row numbers where the G column isn't blank.
• SMALL(..., ROW(A1)): Picks the 1st, 2nd, 3rd, etc., row number from the collected list as you drag the formula down (ROW(A1) increments to ROW(A2), ROW(A3), etc., automatically).
• INDEX('Active Submittals'!$A:$A, ...): Grabs the A column value from the row number returned by SMALL.
• IFERROR(..., ""): Shows an empty cell instead of a #NUM! error when there are no more matching rows to retrieve.

Quick Optimization #

If your Active Submittals sheet has a header row (row 1), adjust the ranges to skip the header (e.g., use 'Active Submittals'!A2:A1000 and 'Active Submittals'!G2:G1000 instead of the entire column). This will make the formula run faster and avoid accidentally pulling your header text.

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