Linking and Relationships

I haven't properly set up the prime input table or the output pivottables yet, but I think the charting is clearly depicted in the attached file. After watching several tutorials, I feel like what I am trying to do is possible, but I haven't quite nailed down the correct process.

There are a handful of potential output PivotTables ( building program, site program, department program, and occupancy report ). All would be PivotTables based on a single data input tab ( Project Program ), where every room / space in a project is listed and catalogued. However, such an input tab would have far too many columns, in my opinion. I feel it would be better to leverage the software to organize and link multiple tables, organizing and creating hierarchy for all the data. The correct phrase may be "linking," "PowerPivot," "data model," or even something else. I have watched many tutorials showing the power of linking tables. However, something that is also emphasized in many of those tutorials is the importance of not inputting data multiple times. If revisions are necessary, one should only have to change that data in one location. Thus, how do I input all the space / room names and numbers in one tab, but have them populate, relate, and link across multiple tables. Another example I read online used student identification numbers to track a student's test scores, assignments, and contact information ( in three separate tables ). One shouldn't have to manually type the student's identification number three times. In my mind, if I sort or filter the data input tab, any PivotTable output retains its integrity. However, if their are multiple tables used for input, I am at a loss both in theory and how exactly to set it up in Excel.

Any help would be awesome! Thanks everyone!

I think you both are on target to my goal. MarvinP, I actually watched that tutorial already. Hahah. Essentially, my question is if my "key" column can be automatically populated from a single table. I don't want to type the "Space Number" twice or track / organize 50 Space Numbers twice ( once on each tab ). Rorya, you seem to indicate the answer is "no." Each set of columns in a relationship is inputted manually, period. MarvinP, agree?

Rorya, agreed. I am fighting it. Hahahah. Excel is not a database. I tested Access because I read Microsoft's guidance between the two platforms. Every architect I know uses Excel for architectural programming. I have never heard of a firm using Access. However, I was shocked when Microsoft's guidance was so clearly toward Access for our purposes. Thus, why I tested it. But it was extremely difficult. I am driven to find the "right" solution, and that little voice in my head still bugs me that there has to be a better way. But I would need significant training for Access and my IT department despises Access. You wouldn't have an opinion between the two platforms. Do you see my Excel file working better as an Access Database? Or, is one Excel data tab and several PivotTable reports the "right" and efficient approach?