Auto-Populate Sheets Based on Master Sheet

Hello,

I'm working with a master list of technicians, the information in which I'd like to auto-distribute to different tabs according to information in the "City" column in the master list. In the attached example, the master list is in the "Technicians" tab. I'd like all of the techs assigned to "Vancouver" in the "City" column to auto-populate to the Vancouver sheet, all of the Calgary techs to auto-populate to the Calgary sheet, etc. If I update the "Technicians" list, I'd like the city tabs to automatically update. For example, if I add a tech who is located in Calgary to the Technicians tab, I'd then like the list in Calgary to auto-add the tech.

I'd also like to choose where the list of techs will appear in the city tabs - I'd like to leave rows at the top of each city sheet so that I can add information about each city.

Hoping that I can accomplish this with formulas but am open to using Macros/VB too.

Thanks!

Example.xlsx

Perhaps like this? Using array formulas, means when ENTER the formula you need to press CTRL-SHIFT-ENTER button together, not ENTER alone

Regards

Hello,
This is really cool and exactly what I am looking for however I am not sure what you did. I am new to excel at this level so it is mostly in plain sight and I am just missing it. I have looked around and I cannot see any formulas or vb scripts. Could you expand on what you did to accomplish the task?

thank-you

Another option is pivot tables. See the attached.

I thought I would give an example of what I am trying to do. I have created a document Master List from which I am trying to build quarter review schedules. I have taken most of the fields off to simplify the example and the quarterly review schedules are from a template in Excel that I grabbed. Additionally on the Master List, I have several dates with the Next Review Date containing a formula that when the Last Review Date is entered it will populate the Next Review Date with the same date plus one year. This maybe way too complicated however what I would like it to do is when the Next Review Date is populated it will place that document on the correct Quarter's worksheet. Crazy right

Pivot tables will definitely get this done.

See the attached.

Thanks Whizbang! I feel stupid for not thinking to put the quarter on the Master List. It seems so obvious now.

Thank you to everyone for your help on this!