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 about Bob, and put them all in a single sheet. I tried to use FILTER to narrow down sheets to the ones where the name cell matched a certain value, and then VSTACK that range from all filtered sheets. But I couldn't get the formatting right, and I'm not sure if that's the right approach. Any help is greatly appreciated.

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)
)

     

5 Replies

  • SergeiBaklan's avatar
    SergeiBaklan
    Diamond Contributor
    Jan 07, 2026

    If I understood correctly you try to do something like

    =LET(
   data, VSTACK(SheetFirst:SheetLast!A1:B10),
   name, FILTER( data, CHOOSECOLS(data,1) = "Bob" ),
   IF(name = "", "", name)
)

    which shall work. It's not clear which formatting you'd like to apply and based on which logic.

    • UnstableMango's avatar
      UnstableMango
      Copper Contributor
      Jan 08, 2026

      Hi, thank you for the response. This works well for what I'm trying to do, but since posting this, I have been informed that they also need to be filtered by a number value. How would I go about adding a second filter to this? I tried the following and it returned a value error
       

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

      (the name is a numerical value because ID #s are being used)

      • m_tarler's avatar
        m_tarler
        Bronze Contributor
        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