Search function across all sheets

[Kotv4]

Dear All,

I was trying to look for something on the internet, unfortunately I could not find what I'm looking for, so I hope you will be able to help me.

I have a huge list of different Groups, which are split into different sheets ( each group = own sheet )
And of course a lot of members that are assigned to these Groups and Divisions in each groups.
Now the question, would it be possible, to create a search function, that would return each group the member is part of with the division as well ?

As an example, I have created a file with 4 sheets.
Let's say, I have for example 1 User ( let's call him baboon with the ID 123 ).
He is a member of Group Apple and Kiwi - where in Apple he is in division 1 and in Kiwi division 1 and 2.
What I'd like to have, once I want to look for the ID, it will return me all the groups he is a part of with the respective divisions.
See the mapping in the excel as a reference.
The Group name is on each sheet in the same cell (B2) as well as the divisions begin on the same Row, basically how the example looks like.
Would something like this be possible ?
If it only would be possible to get at least the group name, that would be already awesome.

Thank you for all your answers and effort in advance.
Best regards,
Kotv4

 

[bosco_yip]

Try..............



1] In cell G5, >> Data >> Data Validation >>

>> Allow : List

>> Source : =$A$2:$A$5

then,

click and selection of Member ID form the Data Validation dropdown list .

2] In F7, copied down :

=IF(G7="","",MAX(F$6:F6)+1)

3] In G7, copied down :

=IFERROR(INDEX({"Apple";"Banana";"Kiwi"},AGGREGATE(15,6,COLUMN($A$1:$C$1)/(COUNTIF(INDIRECT({"'Apple'!A:G","'Banana'!A:G","'Kiwi'!A:G"}),G$5)>0),ROWS($1:1))),"")

4] In J7, copied across to L7 and down :

=IF(ISNUMBER(MATCH(COLUMN(A1),INDEX(COLUMN($A$1:$C$1)/(COUNTIF(INDIRECT("'"&$G7&"'!"&{"A:A","D:D","G:G"}),$G$5)>0),0),0)),COLUMN(A1),"")

Regards
Bosco