Using VSTACK and FILTER to take info in H1 (IF) and spit out C1

[jslocumb]

I am terrible at Excel but trying.

I have a workbook with multiple sheets. I want a formula I can put on Sheet 1 that gives me the following information:
If on Sheets 2-57, the number in H1 is 5 or greater, then give me the info in C1. This will spit out multiple Names from different sheets, and I want to see all of them listed.
I am assuming that you would use VSTACK with FILTER for this, but I can't fathom how.

H1 is itself a formula that gives me a number based on rolling calendar year.
C1 is the person's name.
Basically H1 tells me how many absences they have had in the last 365 days. C1 is their name. I want their name(s) to populate as a list in Sheet1 if the number of absences is 5 or greater.

 

[Dave Patton]

=LET(a,VSTACK(Sheet2:Sheet57!C1:H1),FILTER(a,INDEX(a,,6)>=5))

 

[Dave Patton]

For just the first column, try

=LET(a,VSTACK(Sheet2:Sheet57!C1:H1),TAKE(FILTER(a,INDEX(a,,6)>=5),,1))

 

[Dave Patton]

Try the formulas with just 2 sheets
If you need both the name and the number, consider the second formula.

3D Sumif 365.xlsm
	A	B	C
1
2		Person A
3		Person B
4
5
6		Person A	7
7		Person B	10
8
3a

Cell Formulas
Range	Formula
B2:B3	B2	=LET(a,VSTACK(Sheet2:Sheet3!C1:H1),TAKE(FILTER(a,INDEX(a,,6)>=5),,1))
B6:C7	B6	=LET(a,VSTACK(Sheet2:Sheet3!C1:H1),FILTER(CHOOSECOLS(a,1,6),CHOOSECOLS(a,6)>=5))
Dynamic array formulas.

 

[jslocumb]

I tried inputting that second formula and it gives the error message #calc in the cell instead of the information

 

[jslocumb]

Oh, ok I used this formula =LET(a,VSTACK(Sheet2:Sheet57!C1:H1),FILTER(a,INDEX(a,,6)>=5)) and it gave me the exact thing I was looking for but it also gave me a bunch of cells with 0 in it.
So it looks like [Name] [0] [0] [Current Occurances] [0] [Number I want]. How can I get rid of the columns that have 0 in it?

 

[jslocumb]

Billie Jean Blanchette	0	0	Current Occurances:	0	5
Denise Boivin	0	0	Current Occurances:	0	8
Nicolle Bradbury	0	0	Current Occurances:	0	10

 

[Dave Patton]

With 2 Sheets the following works correctly. I also tried it with 3 sheets.
Please try first with just 2 consecutive sheets.

3D Sumif 365.xlsm
	A	B	C
6		Person B	10
7
8
3a

Cell Formulas
Range	Formula
B6:C6	B6	=LET(a,VSTACK(Sheet2:Sheet3!C1:H1),FILTER(CHOOSECOLS(a,1,6),CHOOSECOLS(a,6)>=5))
Dynamic array formulas.

 

[Dave Patton]

N.B. Choosecols can choose multiple columns.

3D Sumif 365.xlsm
	C	D	E	F
1	Person B	10	Criteria	5
2	Person C	5
3
4
5
6	Person B	10
7	Person C	5
8	Total	15
9
3a

Cell Formulas
Range	Formula
C1:D2	C1	=LET(a,VSTACK(Sheet2:Sheet4!C1:H1),FILTER(CHOOSECOLS(a,1,6),CHOOSECOLS(a,6)>=F1))
C6:D8	C6	=LET(a,VSTACK(Sheet2:Sheet4!C1:H1),GROUPBY(CHOOSECOLS(a,1),CHOOSECOLS(a,6),SUM,,,1,CHOOSECOLS(a,6)>=F1))
Dynamic array formulas.

 

[jslocumb]

So =LET(a,VSTACK(Sheet2:Sheet57!C1:H1),FILTER(CHOOSECOLS(a,1,6),CHOOSECOLS(a,6)>=H1)) works, but it now gives me all of the people and then the number next to them. I only want it to give me the people whose value in H1 is 5 or greater.