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]

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.

 

[jslocumb]

XL2BB

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))

 

[Dave Patton]

Please read post #11 and reply.
I do not have your data and I cannot make suggestions without real information.

 

[jslocumb]

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.

 

[Dave Patton]

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.