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

jslocumb

New Member
Joined
Feb 24, 2025
Messages
7
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.
 
The suggestions work for me with 2 or 3 sheets.
Did you try the formulas with 2 or 3 consecutive sheets?
Did you try the Groupby?

You can paste post #9 into a clean sheet. Click on the icon below the f(x) in the heading, move to your sheet, and paste.

If the formula works correctly with 2 or 3 sheets, then expand the range gradually.

Alternatively post the results of the Vstack for say 10 sheets and we can build a new formula with that information.
You can post the information with the forum's tool named XL2BB.
 
Upvote 0
I put it in for 10 sheets and still it gives me the name on each sheet (C1) and the number of occurrences (H1). I only want it to give me the names and numbers if the number of occurrences (H1) is equal to or greater than five. I don't need the info if they have less than 5 occurrences. The formula works without errors, it just isn't filtering the info I want.

=LET(a,VSTACK(Sheet2:Sheet10!C1:H1),FILTER(CHOOSECOLS(a,1,6),CHOOSECOLS(a,6)>=H1))
 
Upvote 0
Please read post #11 and reply.
I do not have your data and I cannot make suggestions without real information.
 
Upvote 0
I can't use the tool because my company blocks them. I will put images here that hopefully help. The first is what the spreadsheet looks like, and which sheets currently have info I want. The second photo shows a spreadsheet I wouldn't want the info of. On the third image, Formula #1 is =LET(a,VSTACK(Sheet2:Sheet57!C1:H1),FILTER(a,INDEX(a,,6)>=5)) Formula #2 is =LET(a,VSTACK(Sheet2:Sheet10!C1:H1),FILTER(CHOOSECOLS(a,1,6),CHOOSECOLS(a,6)>=H1)) So #1 only gives me the people I want, but has a bunch of 0 columns. #2 gives me the info without the zeroes but now it shows info I don't care about.
want.jpg
dontwant.jpg
results.jpg
 
Upvote 0
Ok You do not want assistance in solving the problem
The suggestions works for me with 2 or 3 sheets.
Did you try the formulas with 2 or 3 consecutive sheets? no answer
Did you try the Groupby? no answer

You can paste post #9 into a clean sheet. Click on the icon below the f(x) in the heading, move to your sheet, and paste. no answer

If the formula works correctly with 2 or 3 sheets, then expand the range gradually. no answer

post the results of the Vstack for say 10 sheets and we can build a new formula with that information.
You can post the information with the forum's tool named XL2BB. no answer I cannot use an image.

Convert the range to values and post the text including headers.
Copy and paste the range of text including headers to the thread.


You could find a way to use XL2BB.

Try your challenge with a current version of Excel 365.
 
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