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

     

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

       

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

        ah, not my brightest moment. Thank you!

Resources