Copy specific cells from multiple spreadsheets onto new spreadsheet

nivekb24

New Member
Joined
Mar 12, 2014
Messages
14
I am using Excel 2007. I have a need to extract certain info from spreadsheetsthat I have saved in a folderand populate a new spreadsheet. This will allow me to quickly search/filter the address so that I can see what Job# was that the site was evaluated on. All of the spreadsheets use this same form as shown in the example below. The cells that I need to extract are the Job# (E8), which changes on every new sheet, and the address info for each site/row. There may be as many as 40 sites on a sheet but as few as 1. Below this example is another example of the spreadsheet and the results I am looking for. Basically I need to have the Job#'s indexed to each of the corresponding sites that were evaluated on that job. I'm sorry about the format of the examples, i don't think I copied them into here correctly but the cell #s are correct... I hope someone can help. My file quantities are growing too quickly for me to be able to search all of the time!

Estimate Page


BCDEFGH
2
3
4
5
6
7
8Job #2014-081203
9
10
11
12
13
14
15
16MANDATORY REQUESTOR INFORMATION
17Site Information
18Site #Bulding/Site NameStreet AddressCityStateZip CodeSite Contact Name
191Site 1100 Pine StreetNew YorkNew York
202Site 2200 Elm StreetBuffaloNew York
213Site 3300 Maple StreetBostonMassachusetts
224Site 4400 Locust StNewburghNew York
235
246
257

<tbody>
</tbody>


Sheet1

ABCDE
1Job#Building/Site NameStreet AddressCityState
2
32014-081203Site 1100 Pine StreetNew YorkNew York
42014-081203Site 2200 Elm StreetNew YorkNew York
52014-081203Site 3300 Maple StreetNew YorkNew York
62014-081203Site 4400 Locust StNew YorkNew York
72014-090101Site 127 First StAlbanyNew York
82014-090101Site 245 Second StAlbanyNew York
92014-091506Site 156 Hoover StTucsonArizona
102014-091506Site 267 Jones RdTucsonArizona
112014-091506Site 323 Phoenix DrTucsonArizona
122014-091506Site 488 South StTucsonArizona

<tbody>
</tbody>
 

Some videos you may like

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.

Watch MrExcel Video

Forum statistics

Threads
1,114,608
Messages
5,548,968
Members
410,886
Latest member
sjohn627
Top