Need to create Master Spreadsheet w/ Directory Referenced.

[Rbechard]

Currently, at work, we use several spreadsheets to compile similar data. We are opening a new office, so I'd like to start fresh with a workbook that would have a master sheet and then department-specific tabs that will be populated with the information in the master sheet. That way, we only need to update one tab and then all the others will populate with the required information. I get by in excel, but I think this is a little too advanced for me. Any advice is appreciated. Example:

Currently, in my position, I need to know Name, Birth date, Lot #, Address, Phone, and Email of all residents.

Construction needs to know Name, Lot#, Model, Basement(Y/N), Settlement Date

Our Activities Director uses the information to create a community directory, for anyone interested in being listed, so I'd like to have a tab that has Name, Lot #, Address, Phone, and Email, but is filled conditionally upon a Y/N on the master sheet.

Each tab would have to be able to be sorted by name, lot number, birth date, or other criteria, and I can't find a way to make this work accurately. If anyone can help, I would really appreciate it. 

[#2]

Hello,

Based on the attached workbook, I discovered that the Workbook Design isn't the "Excel way" of designing a workbook...

You dont really need columns up to AD... You only need about 10 or less columns with all data filled properly... Then, you can have the Master sheet where you can PivotTables or functions to achieve what you desire to achieve

[#3]

@Rbechard 

The solution you receive will depend on the version of Excel you're using.  If you have access to Office 365 and dynamic arrays, this can be done elegantly with FILTER (and even taking it a step further to pull the criteria from the sheet name).

If not on 365, it can still be done.  There are a few ways.