Pull list of names if a value matches in row

[GigaFluxx]

I know the title doesn't give the best explanation but bare with me. I'm newer to excel and having fun learning but sometimes it can be overwhelming.

Anyway, let's say I have 5 sheets. In each sheet are 5 names and in the column beside them are different values (numbers letters, doesn't matter)
Example

Sheet 1:
Adam-----1
Bobby----2
Frank-----1
Julie------3
Sandra---2

Sheet 2:
Sally-----4
John-----1
Paul------3
Zach-----2
Frank S.--2

I want to know if there is a way I can create one sheet that can list all the names from all the sheets, that have value A next to it, then make another that lists all the names from all sheets that have value B beside it. so on and so forth. then if I change my mind on a value, I simply adjust it and the list adapts as normal.

Overall Sheet:

Value 1:
Adam
Frank
John

Value 2:
Bobby
Sandra
Zach
Frank S.

Is this possible?

[prjt]

Out of curiosity, if the data is all the same format is there any reason you can consolidate it all into 1 sheet? (Would make for simpler solutions)

[GigaFluxx]

LOL Proud to be Canadian

The example I gave is a lot more simplified than the actual sheets I'm using. It's actually divided by province, and the list of names are cities/towns with multiple columns for different data. Basically I'm grading the overall data in a 1, 2 or 3 format overall and just want to see which ones are ranked 1, 2 or 3 on my title sheet. If that makes sense.

[FlameRetired]

Try the attached. Is this the idea?

Edit: these are array-entered formulas; they must be committed by pressing Ctrl + Shift + Enter and then filled down.
You can wrap them in an IFERROR(<formula>,"") to suppress the NA's.

[GigaFluxx]

Sort of, but it doesn't appear to pull the info from both sheets> For example, it's listing Julie who = 3 on Sheet 1, but Paul on Sheet 2 also = 3 and he isn't listed there.

[FlameRetired]

Change E1 to a 3. Paul shows up.

I had the list from sheet1 pull names from reference value in B1 and list from sheet2 pull names from reference value in E1.

You want to pull names from both lists with one reference value?

The simplest way would be to put =$B$1 in cell E1. The other would be to change the references in the second formula from $E$1 to $B$1.

Does that meet the need?