Posts by bosco_yip






This part : MID(CELL("filename",$A$1),FIND("]",CELL("filename",$A$1))+1,32)
will return the current sheet name.

In each Sheet of ,"IBM", "Accenture"…... C2, formula copied down :
=IF(SUMIFS(Worksheet!$C:$C,Worksheet!$A:$A,MID(CELL("filename",$A$1),FIND("]",CELL("filename",$A$1))+1,32),Worksheet!$B:$B,[@Name])=[@Value],[@Name],"N/A")

Try,
In "Worksheet" C2, formula copied down :
=VLOOKUP([@[Name (provided)]],INDIRECT("'"&[@[Account (provided)]]&"'!A:B"),2,0)

Thanks for the reply  that works nicely. One question, is there a limit for the range of data this can work on? I've got over 5000 lines of data, I tried it on a small selection and it worked perfectly but failed on the larger range.
Cheers
JoeThis is the limitation of formula solution, for large range of data you should switch to VBA solution or Power Query.

Try this formula solution for Excel 2019 and above.
1] Create 2 define names as per attachment
2] In "Intermate Table" H3, formula copied across down :
=IFERROR(IF(LEFT(H$2)="P",TableRgn/IndexRgn,TableRgn),"")
3] In "Output Table " B9, formula copies across and down :
=SUMIFS(I:I,$H:$H,$A9)
4] In "Output Table " B12, formula copies across :
=SUM(B9:B11)


In L2, formula copied down :
=LEFT(A5,FIND(" ",A5)+2)

Give few lines of example data and the expected result



Thank you it's working perfectly
and one more help if cell have 3 or 4 mobile number how to extract with this formula
One post one question.
Please open a new post for your new question, together with few lines of example data and the expected result.



