L
Legacy 419400
Guest
I have a complicated series of questions. I am trying to confirm I am building these worksheets as efficiently as possible.
I am also probably not the normal user. I am an architect. Over several projects, I have been developing a series of workbooks and worksheets to document what is called a building program. At its simplest, it is a list of rooms. However, most the time, it includes lists of equipment, acceptable finishes, occupancy data, areas, and more.
I have created three files. One holds the primary data, “Building Program”. One holds the occupancy data, “Building Occupancy”. The last file holds all the little details concerning each room, “Space Data Sheets”.
Each workbook references the other. I have used data drop-boxes and VLOOKUP to automatically fill many cells to streamline the documentation process. I feel the files are rather robust and impressive for my industry.
However, I have reached the point where I must link the primary worksheet in the “Building Program” to the primary worksheet in the “Building Occupancy” file. I have considered an array, tables, and pivot tables. I have also looked into power pivot and power view, but my firm is using Excel 2010 for the time being. I also don’t think I truly need a pivot table; I am not rotating rows or columns at all. I simply need the “duplicate” the room names, numbers, and areas to the second worksheet so I can associate the appropriate occupancy data. However, I think if I were to shuffle ( alphabetize or sort ) the “Building Program”, I am not confident the rows in “Building Occupancy” would dynamically update. VLOOKUP would shuffle the room names and numbers to maintain proper relationships, but the new occupancy data would remain stationary. I am not sure.
I have three snapshots of the three workbooks, but this forum doesn't seem to allow easy attachments. I wish I could post the files themselves.
I am also probably not the normal user. I am an architect. Over several projects, I have been developing a series of workbooks and worksheets to document what is called a building program. At its simplest, it is a list of rooms. However, most the time, it includes lists of equipment, acceptable finishes, occupancy data, areas, and more.
I have created three files. One holds the primary data, “Building Program”. One holds the occupancy data, “Building Occupancy”. The last file holds all the little details concerning each room, “Space Data Sheets”.
Each workbook references the other. I have used data drop-boxes and VLOOKUP to automatically fill many cells to streamline the documentation process. I feel the files are rather robust and impressive for my industry.
However, I have reached the point where I must link the primary worksheet in the “Building Program” to the primary worksheet in the “Building Occupancy” file. I have considered an array, tables, and pivot tables. I have also looked into power pivot and power view, but my firm is using Excel 2010 for the time being. I also don’t think I truly need a pivot table; I am not rotating rows or columns at all. I simply need the “duplicate” the room names, numbers, and areas to the second worksheet so I can associate the appropriate occupancy data. However, I think if I were to shuffle ( alphabetize or sort ) the “Building Program”, I am not confident the rows in “Building Occupancy” would dynamically update. VLOOKUP would shuffle the room names and numbers to maintain proper relationships, but the new occupancy data would remain stationary. I am not sure.
I have three snapshots of the three workbooks, but this forum doesn't seem to allow easy attachments. I wish I could post the files themselves.
