Forum Discussion

UnstableMango's avatar
UnstableMango
Copper Contributor
Jan 06, 2026
Solved

Getting the Same Range from an List of Sheets that Match a Variable

Put simply, I have an indefinite number of sheets that regard different people. I need to collect the same range from each sheet that reference a specific person. I.E. Collect A1:B10 from all sheets ...
excel
Formulas and Functions
  • m_tarler's avatar
    m_tarler
    Jan 08, 2026

    the problem is that in the 2nd FILTER you are still using "data" which presumably has more rows than "quarter" since you just filtered a bunch of row out.  here is the 'simple' fix:

    =LET(
   data, VSTACK(Blank:QCs!AH16:AN71),
   quarter, FILTER(data, CHOOSECOLS(data,1) = 1),
   name, FILTER(quarter, CHOOSECOLS(quarter,2) = 145),
   IF(name = "", "", name)
)

    but there is no need to do 2 FILTER lines.  you can do both in one filter:

    =LET(
   data, VSTACK(Blank:QCs!AH16:AN71),
   name, FILTER(data, (CHOOSECOLS(data,1) = 1)*(CHOOSECOLS(data,2) = 145)),
   IF(name = "", "", name)
)

     

NikolinoDE's avatar
NikolinoDE
Platinum Contributor
Jan 07, 2026

Core limitation…

Excel functions like FILTER, VSTACK, LET, LAMBDA cannot loop through sheet names dynamically.

So this cannot work natively:

=VSTACK(A1:B10 from all sheets where Name="Bob")

Excel formulas do not have a “foreach sheet” capability.

 

VBA SOLUTION (Best if you already use VBA)

If you’re comfortable with VBA and want formatting preserved:

Sub CollectPersonRanges()

    Dim ws As Worksheet
    Dim tgt As Worksheet
    Dim nextRow As Long
    Dim person As String

    person = "Bob"
    Set tgt = Worksheets("Summary")
    nextRow = 1

    For Each ws In Worksheets
        If ws.Range("A1").Value = person Then
            ws.Range("A1:B10").Copy tgt.Cells(nextRow, 1)
            nextRow = nextRow + 10
        End If
    Next ws

End Sub

works well because…

Simple, Keeps formatting, Fast, Clear logic.

-------------------------------------------------------

Power Query (Not my strength, but the best possible solution)

If:

  • You have many sheets
  • Sheet count changes
  • You want a robust, maintainable solution

Power Query is the right tool.

This is exactly what Power Query is built for.

 

 

My answers are voluntary and without guarantee!

 

Hope this will help you.

 

Was the answer useful? Mark as best response and like it!

This will help all forum participants.

Resources