Is it possible to display a list of names (sheet names) where a data criteria is met on other sheets?

Question

Is it possible to display a list of names (sheet names) where a data criteria is met on other sheets?

Anonymous

Example - Sheets called Red, White, Blue, Green .. and consolidate

Red A1 = Y Red A2 = N

White A1 = Y White A2 = Y

Blue A1 = N Blue A2 = Y

Green A1 = Y Green A1 = N

Red

	A
1	Y
2	N

White

	A
1	Y
2	Y

Blue

	A
1	N
2	Y

Green

	A
1	Y
2	N

I can get totals of "Y" answer to display on consolidate i.e. Consolidate A1 = 3, A2 = 2

But what I'd like to display is the sheet names that answered "Y"

So Consolidate A1 would display: Red A2 would display: White

                                                   White                                        Blue

                                                   Green

Consolidate

	A
1	Red<br><br>White<br><br>Green
2	White<br><br>Blue

Hope this makes sense?

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

Answer accepted by question author

HansV 462.7K MVP Volunteer Moderator

In A1 on the Consolidate sheet:

=TEXTJOIN(", ", TRUE, IF(Red!A1="Y", "Red", ""), IF(White!A1="Y", "White", ""), IF(Blue!A1="Y", "Blue", ""), IF(Green!A1="Y", "Green", ""))

0 comments

3 additional answers

Answer 1

HansV 462.7K MVP Volunteer Moderator

Change the formula to

=TEXTJOIN(CHAR(10), TRUE, IF(Red!A1="Y", "Red", ""), IF(White!A1="Y", "White", ""), IF(Blue!A1="Y", "Blue", ""), IF(Green!A1="Y", "Green", ""))

and turn on Wrap Text for A1 and A2

0 comments

Answer 2

Anonymous

@HansV MVP - thank you .. any way to get the resulting cell to display names in a list format?
I thought formatting and wrap text might work but not as desired.
No probs if not.

0 comments

Answer 3

Anonymous

Thank you HansV MVP - brilliant!

0 comments