Forum Discussion
Is there a similar way of structured references with additional rows & columns from single table?
I am trying to make a small employee tracker/database for a federal job.
- I would like to have a single master table that has 3 columns and 50 rows of employee info:
Column A: SECTION
Column B: RANK
Column C: NAME (last, first)
- I want this master sheet to be the only table I update employee info as they leave, get promoted, move sections, and so on.
- This info would need to be on multiple other tables. Each new table has lots of other columns. How can these new columns' rows info be associated with the 'master table's' info as that info changes? All of the additional info will be either dates or text info.
Would this be a Power Query with a Join Kind? Excel relationship? VBA?
- Doing this as a structured reference entails having to manually update/remove info as employees leave on each subsequent table (roughly 40 tables). Or should I have a single master table with every column then used as a reference for the specific info I would need?
2 Replies
villa570 My first though would be NOT to delete employees that have left. Add one more column to the master for their Status (active/inactive). Then I would also add an ID column to all your tables with unique ID's for each employee, to avoid the risk of mixing up employees that might have the same name.
You can use Power Query and/or Power Pivot to merge or relate these tables and create reports for only the active employees.
Edit: But as mtarler suggested, perhaps better to just have one big master table.
- You could do 1 master table if they are all based on the same data set (i.e. same set of employees) and then just have different reports on other tabs.
If you want different tabels then you could have a linking ID and then use LOOKUP function (i.e. XLOOKUP is latest and 'greatest') to pull in the other information into each of the other tables. I would recommend Format as a Table (button found on Home tab). Then for example the 'base' table above you could call Empoyees and let's say you add a column called ID then the other tables would call something like this to get the corresponding section for the ID in 'this' table from the Employee table:
=XLOOKUP( [ID], Employees[ID], Employees[Section], "n/a")
