How do I search multiple sheets with multiple functions, but have the input on a single sheet?

Question

I have the function: =TEXTJOIN(",",TRUE,TRANSPOSE(FILTER($A:$A,$C$1=$B:$B) on one sheet. I want to search through different sheets but have the function value displayed on the first sheet where the formula is.

For Example:

On Sheet 1, I have =TEXTJOIN(",",TRUE,TRANSPOSE(FILTER($A:$A,$C$1=$B:$B)&FILTER('Sheet 2'!$A:$A,$C:$1=$B:$B) and it works but when I copy the same formula but add sheets 3 and 4, it doesn't work.

If someone could help that would be great! Thank you for your time and I hope you have a great rest of your day!

Answer 1

Try

=TEXTJOIN(",", TRUE, VSTACK(TRANSPOSE(FILTER(Sheet 1'!$A:$A, 'Sheet 1'!$B:$B=$C$1), FILTER('Sheet 2'!$A:$A, 'Sheet 2'!$B:$B=$C$1), FILTER('Sheet 3'!$A:$A, 'Sheet 3'!$B:$B=$C$1), FILTER('Sheet 4'!$A:$A, 'Sheet 4'!$B:$B=$C$1))))