Posts by bosco_yip
We will be implementing some important changes during 25th and 26th May 2024 which may result in an outage period of the website. We anticipate this process may take a couple of hours and so we apologise in advance for any inconvenience.






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






