Pulling Specific Columns from Raw Data (needs to be duplicable for changing raw data)

[MrQue]

Hello - I am having issues coming up with a repeatable template for pulling specific columns from raw data that changes based on location and time.

Here is my example:

Weekly Raw data is pulled for various locations (let's say Massachusetts and Maryland) So i have two different workbooks containing the raw data: Novweek1RawDataMass and Novweek1RawDataMary

Let's start with Novweek1RawDataMass - Here is what that sheet looks like:

[TABLE="width: 500"]
<tbody>[TR]
[TD]First Name[/TD]
[TD]Last Name[/TD]
[TD]City[/TD]
[TD]Phone[/TD]
[TD]Email[/TD]
[/TR]
[TR]
[TD]Bob[/TD]
[TD]Gruber[/TD]
[TD]Somewhere[/TD]
[TD]999[/TD]
[TD]blah@yep[/TD]
[/TR]
[TR]
[TD]Gru[/TD]
[TD]Wallace[/TD]
[TD]yeppers[/TD]
[TD]876[/TD]
[TD]gru@yep[/TD]
[/TR]
[TR]
[TD]Daphne[/TD]
[TD]Mystery[/TD]
[TD]Vans[/TD]
[TD]871[/TD]
[TD]scraw@yep[/TD]
[/TR]
</tbody>[/TABLE]

Now I need to create a spreadsheet that pulls just the columns First Name, Last Name, and Email (In reality my raw data ranges from A1 - EO1300)

I started by just creating a new worksheet in the raw data workbook and using a simple ="A2". However, my Raw data file changes names each week and I can't copy over this equation as it still tries to pull from the old worksheet. Any thoughts on how to do this so it is repeatable each week?

Thanks in advance.

 

[Warship]

set up a folder to use, then loop thru all files in that folder without regard to name. if the file naming convention you show is constant, you can read the Location name in the file name and apply whatever template you need for that location.