Search for a Name or Phone number among multiple sheets

Ramadan

Board Regular
Joined
Jan 20, 2024
Messages
142
Office Version
  1. 2021
Platform
  1. Windows
I have a workbook with 9 sheets subject to be increased in the future, All the sheets have the same table design, format and the same starting row number
in cell "B2" in each sheet there is a code as a name for the contents like "NH01,NH02,NH03, etc...

What I need is to be make a formula or something in any sheet of them that make me able to search in theses sheets for any name or phone number (inserted as text) between columns ("D:M") and if there is a match to let me know the sheet code in "B2" to know the sheet name that has the match and the row number in the table or even the row number in the excel sheet
for your kind information there might be more than one result - is that possible ?

Thank you in advance for your help
 
Here is a minisheet
copy.xlsm
ABCDEFGHIJKLMN
2NH04
30Hanady Hoot
4TRUE
5Westridge
6
7SUnit No.Owner NamePhone No.Auth. Date Authorized Personnel
8Name Phone No.Name Phone No.Name Phone No.Name Phone No.
9Column1Column2Column3Column4Column5Column6Column7Column8Column9Column10Column11Column12Column13
101RT08 - 75 Mona Rashid0100507644421.03.24Monazid Aly01004003553Gamila Sodre01009962552Amira shkory01062162153محمد وحيد مأمون01000904840
112RT08 - 16 Sawsan Edress12.11.21Faten KolaNadeen Nor
123RT08 - 95Mamdouh taher28.11.21
134LS WF 25R Nabila Samy14.05.23
145RW08 - 113Nadia Aziz08.05.22Salma HatemJessica Hadde
156RW09 - 119Entsar Rod02.01.22
167RW09A - 78 Mai Helmy0100172288637/1Ahmed Bassam01222195601Radwa Samy0100380572
178W12 - 22Lolo Abode03.09.23
189W15 - 22Emy Samy05.01.22Marina MedhatDalia Hussein
1910W18 - 33Sherin Wasif03.09.23
2011W21 - 23Korashi taha
West
Cell Formulas
RangeFormula
C3C3=COUNTIF(West[[Column6]:[Column12]],"*"&D3&"*")
D4D4=IFERROR(VLOOKUP($D$4,Carnell[#All],'Carnell'!$B$2),"No Match")=IFERROR(VLOOKUP($D$4,Ivory[#All],Ivory!$B$2),"No Match")
B10:B20B10=IF([@Column2]<>"",ROW()-ROW($B$9),"")
Cells with Conditional Formatting
CellConditionCell FormatStop If True
B10:T71Expression=SUMPRODUCT(('Stop Work'!$D$8:$D500=$C10)*('Stop Work'!$K$8:$K500="Open")*('Stop Work'!$C$8:$C500=$B$2)*($T10=""))textNO
C10:C365Expression=AND(ISBLANK($P10),$R10<>"",$T10="")textNO
C10:C365Expression=$T10<>""textNO
C10:C365Expression=AND($D$3<>"",ISNUMBER(SEARCH($D$3,$G10&$I10&$K10&$M10)))textNO
C10:C365Expression=(AND($P10<>"",$E10<>"",$R10="",$T10=""))textNO
C10:C365Expression=(AND($P10<>"",$E10="",$R10="",$T10=""))textNO
C10:C365Expression=AND(ISBLANK($P10),ISBLANK($R10),ISBLANK($T10))textNO
C10:C365Expression=(AND($P10<>"",$R10<>"",($T10="")))textNO
B10:T365Expression=AND(ISBLANK($P10),$R10<>"",$T10="")textNO
B10:T365Expression=$T10<>""textNO
B10:T365Expression=AND($D$3<>"",ISNUMBER(SEARCH($D$3,$G10&$I10&$K10&$M10)))textNO
B10:T365Expression=(AND($P10<>"",$E10<>"",$R10="",$T10=""))textNO
B10:T365Expression=(AND($P10<>"",$E10="",$R10="",$T10=""))textNO
B10:T365Expression=AND(ISBLANK($P10),ISBLANK($R10),ISBLANK($T10))textNO
B10:T365Expression=(AND($P10<>"",$R10<>"",($T10="")))textNO
H12Expression=SUMPRODUCT(('Stop Work'!$D$8:$D500=$C10)*('Stop Work'!$K$8:$K500="Open")*('Stop Work'!$C$8:$C500=$B$2)*($T10=""))textNO
B184:T359,B183:J183,M183:T183,B11:T182Expression=AND(ISBLANK($P11),$R11<>"",$T11="")textNO
B184:T359,B183:J183,M183:T183,B11:T182Expression=$T11<>""textNO
B184:T359,B183:J183,M183:T183,B11:T182Expression=AND($D$3<>"",ISNUMBER(SEARCH($D$3,$G11&$I11&$K11&$M11)))textNO
B184:T359,B183:J183,M183:T183,B11:T182Expression=(AND($P11<>"",$E11<>"",$R11="",$T11=""))textNO
B184:T359,B183:J183,M183:T183,B11:T182Expression=(AND($P11<>"",$E11="",$R11="",$T11=""))textNO
B184:T359,B183:J183,M183:T183,B11:T182Expression=AND(ISBLANK($P11),ISBLANK($R11),ISBLANK($T11))textNO
B184:T359,B183:J183,M183:T183,B11:T182Expression=(AND($P11<>"",$R11<>"",($T11="")))textNO
C2Expression=D2=""textNO
C3Expression=D3=""textNO
 
Upvote 0

Forum statistics

Threads
1,226,883
Messages
6,193,488
Members
453,803
Latest member
hbvba

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top