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