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

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.

Sheet structure assumption

Each sheet:

• Has a cell (say A1) with the person’s name
• Has data in A1:B10 (or similar)

Steps

1. Data → Get Data → From Other Sources → Blank Query
2. Open Advanced Editor
3. Paste this (adjust ranges as needed):

let
    Source = Excel.CurrentWorkbook(),
    SheetsOnly = Table.SelectRows(Source, each [Kind] = "Sheet"),

    AddName = Table.AddColumn(
        SheetsOnly,
        "Person",
        each try Excel.Workbook([Content], false){0}[Data]{0}[Column1] otherwise null
    ),

    FilterBob = Table.SelectRows(AddName, each [Person] = "Bob"),

    ExpandData = Table.TransformColumns(
        FilterBob,
        {"Content", each Table.Range(_, 0, 10)}
    ),

    Combine = Table.Combine(ExpandData[Content])
in
    Combine

Works well because…

Automatically pulls A1:B10
From every sheet for Bob
Sheet count doesn’t matter
Refresh updates everything

This is exactly what Power Query is built for.

(Formula is not tested...)

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.