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

Jan 07, 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

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

Copper Contributor

Jan 07, 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)

Forum Discussion

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