Data to extract from multiple tab

[Shan54321]

Hi ,

I have a summary tab ,I need only bounce cheque details from following tabs to main tab.

Any formula can help me to make it easier.

[alansidman]

Attached is a file that I created using Power Query to extract Returned Checks.

I imported your file (both tabs) to a new workbook. Appended the two sheets to each other and then did some data manipulation. It is all available to you in the files.

After appending the files, here is the Mcode

Please Login or Register  to view this content.

[Bo_Ry]

Please try at
A2
=IF(B2="","",INDEX(INDIRECT("'"&B2&"'!A:A"),AGGREGATE(15,6,ROW('0050'!$B$4:$B$99)/ISNUMBER(FIND("Return",INDIRECT("'"&B2&"'!B4:B99"))),COUNTIF(B$2:B2,B2))))

B2
=IF(ROWS(B$2:B2)<=COUNT(INDEX(FIND("Return",'0050'!$B$4:$B$99),)),"0050",IF(ROWS(B$2:B2)-COUNT(INDEX(FIND("Return",'0050'!$B$4:$B$99),))<=COUNT(INDEX(FIND("Return",'0051'!$B$4:$B$99),)),"0051",""))

C2
=IF(B2="","",INDIRECT("'"&B2&"'!C2"))

D2
=IF(B2="","",LEFT(TRIM(RIGHT(SUBSTITUTE(INDEX(INDIRECT("'"&B2&"'!B:B"),AGGREGATE(15,6,ROW('0050'!$B$4:$B$99)/ISNUMBER(FIND("Return",INDIRECT("'"&B2&"'!B4:B99"))),COUNTIF(B$2:B2,B2))),":",REPT(" ",99)),99)),6))

E2
=IF(B2="","",INDEX(INDIRECT("'"&B2&"'!C:C"),AGGREGATE(15,6,ROW('0050'!$B$4:$B$99)/ISNUMBER(FIND("Return",INDIRECT("'"&B2&"'!B4:B99"))),COUNTIF(B$2:B2,B2))))

[Shan54321]

Hi Bo ,

Thanks it will help . One question if want more tabs to add , so which formula I need to add .Do I have to change in all formula or only in B2 .

[Bo_Ry]

Yes, Please Key sheet name at G1:Gxx

Add name range Sheetlist =$G$1:INDEX($G:$G,COUNTA($G:$G))

B2
=IFERROR(INDEX(Sheetlist,MATCH(0,INDEX(N(COUNTIF(B$1:B1,Sheetlist)=COUNTIFS(INDIRECT("'"&Sheetlist&"'!B4:B99"),"*Return*")),),)),"")

[alansidman]

Shan54321

No comment about Power Query Solution? Did you even try it or did you choose to ignore it? Common courtesy says a response is in order.

[Shan54321]

Dear Alan ,

I Haven't tried .I want a step by step to follow the power query.

Thanks alan.

[alansidman]

Click on the Power Query Links in my signature block.