Formula to pick up location data within multiple worksheets

[Ange2010]

Good afternoon,

I am trying to create a formula where a cell contains location details for example, NSW - Crows Nest, and VIC - Flinders Lane - and have NSW or VIC populated in another cell. So the State (NSW or VIC) is the specific text I want the formula to pick up. I have multiple locations and my table will grow to more than 300 rows.

In addition to this, when the Location cell has been populated with the State, I need to develop a tab per State to read the data from the master tab (where the abovementioned cells are created). There are seven columns/data I need to pull across for that specific State.

**Updated file link Here is a usable link to the file.

Thanking you in advance,

[#2]

@Ange2010 

Kinda hard to "see the attached file" when the link takes one to this

[#3]

@mathetes my apologies for that.
Here is a usable link to the file. 

[#4]

@Ange2010 

It was a usable link, for which I'm grateful. Still was "read-only" however, so I copied some of the data to the attached and think I've done what you were wanting to do. Write back if not. The second sheet makes use of the FILTER function , which requires the most recent version of Excel.

[#5]

Extremely grateful - thank you. I will have a look at this now and come back to you if I have any issues.

[#6]

@mathetes thank you very much. When I used your formula in sheet 1 (National) in my master file (link below), it worked perfectly.

My master file, sheet 1 (National) is pulling data from another workbook. For sheets 2-5, I was initially thinking I would have a sheet per State as our objective is to have a user friendly By State document to upload. Could I use a FILTER function here? If so, I would need assistance please.

This is the master file 

[#7]

@Ange2010 

Your formula in column I of the master sheet is yielding #VALUE errors in some cases because of the inconsistency in what you have in column H. And that inconsistency is due to a mixing of kinds of information: "Online" is not a location, rather it's a means of delivery or a medium (however you want to characterize it), but not location per se. Nevertheless, if you want to keep it that way, revise the formula to read thus:

=IFERROR(LEFT(H6,FIND("-",H6)-2),H6)

That said, I can't see any reason why FILTER wouldn't work. Here's a video that explains it (it's how I learned it a year or so ago). I'd recommend you watch it, then come back if you still need additional help.

https://www.youtube.com/watch?v=9I9DtFOVPIg

Here, too, is a good website for learning new functions: https://www.youtube.com/watch?v=9I9DtFOVPIg