Forum Discussion

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

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

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

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
Copper Contributor
Jan 08, 2026
ah, not my brightest moment. Thank you!