How to search across a Workbook and copy the returned data to a worksheet?

[bluby]

Hi everyone, I'm going through my bank records from 2014 and Wells Fargo only provides statements in the form of PDF's. I was able to convert the PDF to excel and the output is great. However, the converter I used created an individual worksheet for each page that exists in the PDF. Not a problem really.

However...

In Excel I was hoping to Search (CTRL F) for the term "Shell Gas" and have it search the entire Workbook. I have gotten this far and I see the results in the FIND/REPLACE window.

The problem is I need a way to copy all that data to a single Worksheet so I can then Auto Sum it.

In a nutshell: I want to search across a workbook and copy the search results to a single worksheet that I can auto sum the totals.

Is this possible? I can't imagine going through every line by line.

Thanks!

[FDibbins]

Hi, welcome to the forum

Instead of copying all the data to a new sheet, why not just sum it all to a new sheet?

This is fairly easy to do, follow these steps...

1. create a list of sheet names. If you dont have that many, just type them into the list
If you have lots, then use the UDF (User Defined Function)
...Create a range name and call it SheetNames (rght click any cell, select Define Names)
...in the Refers to: =REPLACE(GET.WORKBOOK(1),1,FIND("]",GET.WORKBOOK(1)),"")
...click OK

To get a list of sheet names, put this in a cell and copy down (started in A2)...
=IFERROR(INDEX(Sheetnames,ROWS($A$2:A2)),"")

If you want to be able to click the cell and go to that worksheet, change that formula to this...
=IFERROR(HYPERLINK("#"&"'"&INDEX(Sheetnames,ROWS($A$2:A2))&"'!A1",INDEX(Sheetnames,ROWS($A$2:A2))),"")

Now you have a list of sheet names. To do the sum, use this (adjust ranges as needed)...

Sum across sheets =SUMPRODUCT(SUMIF(INDIRECT("'"&Sheetnames&"'!T6:T1000"),C794,INDIRECT("'"&Sheetnames&"'!M6:M1000")))

[FDibbins]

Hi, welcome to the forum

Instead of copying all the data to a new sheet, why not just sum it all to a new sheet?

This is fairly easy to do, follow these steps...

1. create a list of sheet names. If you dont have that many, just type them into the list
If you have lots, then use the UDF (User Defined Function)
...Create a range name and call it SheetNames (rght click any cell, select Define Names)
...in the Refers to: =REPLACE(GET.WORKBOOK(1),1,FIND("]",GET.WORKBOOK(1)),"")
...click OK

To get a list of sheet names, put this in a cell and copy down (started in A2)...
=IFERROR(INDEX(Sheetnames,ROWS($A$2:A2)),"")

If you want to be able to click the cell and go to that worksheet, change that formula to this...
=IFERROR(HYPERLINK("#"&"'"&INDEX(Sheetnames,ROWS($A$2:A2))&"'!A1",INDEX(Sheetnames,ROWS($A$2:A2))),"")

Now you have a list of sheet names. To do the sum, use this (adjust ranges as needed)...

Sum across sheets =SUMPRODUCT(SUMIF(INDIRECT("'"&Sheetnames&"'!T6:T1000"),C794,INDIRECT("'"&Sheetnames&"'!M6:M1000")))

[bluby]

Wow guys this is WAY over my head. I'm just a beginner excel user lol

[bluby]

Hi, welcome to the forum

Instead of copying all the data to a new sheet, why not just sum it all to a new sheet?

This is fairly easy to do, follow these steps...

1. create a list of sheet names. If you dont have that many, just type them into the list
If you have lots, then use the UDF (User Defined Function)
...Create a range name and call it SheetNames (rght click any cell, select Define Names)
...in the Refers to: =REPLACE(GET.WORKBOOK(1),1,FIND("]",GET.WORKBOOK(1)),"")
...click OK

To get a list of sheet names, put this in a cell and copy down (started in A2)...
=IFERROR(INDEX(Sheetnames,ROWS($A$2:A2)),"")

If you want to be able to click the cell and go to that worksheet, change that formula to this...
=IFERROR(HYPERLINK("#"&"'"&INDEX(Sheetnames,ROWS($A$2:A2))&"'!A1",INDEX(Sheetnames,ROWS($A$2:A2))),"")

Now you have a list of sheet names. To do the sum, use this (adjust ranges as needed)...

Sum across sheets =SUMPRODUCT(SUMIF(INDIRECT("'"&Sheetnames&"'!T6:T1000"),C794,INDIRECT("'"&Sheetnames&"'!M6:M1000")))

Thanks admin! I'm just a beginner tho

[bluby]

Any other way? This is way over my head.