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

jslocumb

New Member
Joined
Feb 24, 2025
Messages
1
Office Version
  1. 365
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.
 
For just the first column, try

=LET(a,VSTACK(Sheet2:Sheet57!C1:H1),TAKE(FILTER(a,INDEX(a,,6)>=5),,1))
 
Upvote 0
Try the formulas with just 2 sheets
If you need both the name and the number, consider the second formula.

3D Sumif 365.xlsm
ABC
1
2Person A
3Person B
4
5
6Person A7
7Person B10
8
3a
Cell Formulas
RangeFormula
B2:B3B2=LET(a,VSTACK(Sheet2:Sheet3!C1:H1),TAKE(FILTER(a,INDEX(a,,6)>=5),,1))
B6:C7B6=LET(a,VSTACK(Sheet2:Sheet3!C1:H1),FILTER(CHOOSECOLS(a,1,6),CHOOSECOLS(a,6)>=5))
Dynamic array formulas.
 
Upvote 0

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top