auto populating rows from one sheet into other sheets based on criteria

[Ash-7]

I’m wondering how to have rows of information from a main sheet automatically populated into various other sheets within the same workbook. The main sheet will continually be updated and added to, so these changes need to be reflected on the other sheets as well. My main “All Sites” spreadsheet contains sites across Canada & the US and I’d like to create individual sheets for each province/state within the same document which automatically populate information from the “All Sites” into the appropriate province/state sheet. For example:

All rows with Alberta in column C of the “All Sites” sheet are automatically populated into the “Alberta” sheet;

All rows with Michigan in column C of the “All Sites” sheet are automatically populated into the “Michigan” sheet; etc.

Thank you!

[#2]

Hi @Ash-7

I'd use Power Query to do this if you have Excel 2016 or are able to download Power Query for Excel 2010/2013

See the attached file,  I've populated the 1st 2 sheets,  I can explain this further if this seems like a suitable solution.   You just right-click and refresh the green tables to update them (or click the Data >Refresh All button)

[#3]

Hi @Wyn Hopkins

Thank you- that seems to do the trick! If new rows were added, would they captured (once the table was refreshed)? This is a dynamic list and we will continually be updating and adding to it. 

Yes, please explain further... I'm pretty rudimentary in my excel skills. And yes, I'm using 2016.

[#4]

Hi @Ash-7

Yes new rows will be captured as long as they are added to the "Table".  The table is that formatted block that should automatically get bigger as you type directly underneath it.

Power Query is found under the Data ribbon.  You can click on the button called Queries and Connections

As you see I have done 2 queries AB and BC  you would need to duplicate and edit these for the other regions 

To do this double click on one query in the right hand window,  e.g. BC this opens the Power Query editor

1. Expand the left hand pane and right click on BC and Duplicate

2. Rename this as MB for example and change the formula bar to reference MB rather than BC

3. Click the close and Load option and chose the sheet and cell you want this filtered table to be loaded to

Hope that helps