I've got two tables in Excel, one containg staff info, and another being a table of course info. I want to have a 3rd table automatically generated which has the staff names listed in the rows of the first column (taken from the "Staff" source table), and the course codes (from the "Courses" source table) used as the column headers. This new table can then be used to allocated teaching hours. When a new staff member is added or a new course is added I need this 3rd table to dynamically update.
My old fashioned way of doing this using lookups and refrences to generate the third dynamic table breaks when staff or courses are added in the middle of the source tables. E.G. If a new course in the middle of the "Course Info" table, the dynamic table will move the course codes across the top correctly, but the values of allocated teaching hours in the rows below stay in the same place.
I've just started trying to understand tables in excel in an effort to work this out, and I get the feeling there must be a "right way" to do this, but I can't seem to work it out.
Any advice is greatly appreciated.
i woud look at power query.
assume :
Select the tables and choose "From Table Range":
Then in the Power Query tool "merge as new":
Selecting the tables and choosing the key:
This will give you :
Close and load, and you have your table in a new sheet.