Search entire workbook with around 10-15 sheets and update a cell listing which sheets contains that information.

[Rotherzz]

I have a workbook that contains around 10-15 sheets which all contain employee information based on what they are currently alligned to.

I was wondering if anyone knows of a formula or a way to search through all of the sheets and if the employee details are found on any of the sheets, that the cell (where the formula is) lists all sheets that has the data in.

The formula will be placed in E2:E6 (Alligned tab picture).
The end goal would ideally look like E2:E6 (End product picture). Incase i have explained it poorly, hopefully that picture will make it clearer.

The pictures are of a quick draft i have made up to keep my work details safe.

Thank you in advance!

 

[SanjayGMusafir]

I have a workbook that contains around 10-15 sheets which all contain employee information based on what they are currently alligned to.

I was wondering if anyone knows of a formula or a way to search through all of the sheets and if the employee details are found on any of the sheets, that the cell (where the formula is) lists all sheets that has the data in.

The formula will be placed in E2:E6 (Alligned tab picture).
The end goal would ideally look like E2:E6 (End product picture). Incase i have explained it poorly, hopefully that picture will make it clearer.

The pictures are of a quick draft i have made up to keep my work details safe.

Thank you in advance!

That is somewhat possible. It all depends how and what kind of data is stored in those 10-15 sheets.

 

[Rotherzz]

Thank for for the reply!

It will be the same details that are listed on the main tab (employee information). The others tabs just use a VLOOKUP function to port all of the details to the other tabs depending on what theyre currently alligned to.

Ideally i just need the new formula to search for the employee number on all of the sheets and all of the sheets theyre on listen on the alligned tab, just to easily see who is alligned to what more efficiently.

Hope this helps.

 

[SanjayGMusafir]

Thank for for the reply!

It will be the same details that are listed on the main tab (employee information). The others tabs just use a VLOOKUP function to port all of the details to the other tabs depending on what theyre currently alligned to.

Ideally i just need the new formula to search for the employee number on all of the sheets and all of the sheets theyre on listen on the alligned tab, just to easily see who is alligned to what more efficiently.

Hope this helps.

Nest XLOOKUP Function within IFS Function in Few Helper Column(s)
Then you can merge those results in 1 Column using TRIM Function

Just to give you some idea -

=IFS(XLOOKUP("Name1",Sheet1_Column,Any_Return_Column)<>"","1",XLOOKUP("Name1",Sheet2_Column,Any_Return_Column)<>"","2",True,"")

Also, We can nest XLOOKUP within XLOOKUP but that won't work for you.

To make Formula shorter and avoid repetitive things LAMDA is a perfect solution using LET Function

With the amount of information you have shared, I can only give you idea how to populate Formula(e).

 

[Rotherzz]

thank you for the reply again.

Im not sure i understand. will "Name1" be the employee number? and do i have to change the sheet number to the name?

 

[SanjayGMusafir]

thank you for the reply again.

Im not sure i understand. will "Name1" be the employee number? and do i have to change the sheet number to the name?

Simple XLOOKUP for Employee Number on Sheet1 to give you result "1" and so on...

You are already using and familiar to Lookup Function - It's same but easier

 

[Rotherzz]

I can’t seem to get it to work, I’m using the formula you provided and changed the values to match my sheet but it either shows a value error or name error