Automatic update of Information from One sheet to another sheet in One Excel. |General Excel Questions & Answers|Excel Forum|My Online Training Hub

Hello,

Thanks for the sample file, makes it a lot easier to understand the issues.

As I don't know what version of Excel you have, I have given two examples on how to solve your first issue, using XLOOKUP or INDEX & MATCH.
As you can see in my uploaded file I have combined your three different sheets into one in TEAMS sheet, I see no reason to split them up in different sheets.

Regarding your second issue, I don't really follow. I have though given it a try to answer it, thus created a new sheet named Empl in the uploaded file. What I do is to connect the team name to each emloyee and then in Individual Information sheet I do a lookup to find the team name that the employee has. When you change the team in Empl sheet it will change also in the Individual Information sheet. But as said, I don't really know if this is what you looked for.

Br,
Anders

Hello,

Ok, thanks for clarifying.

In attached file I propose a different approach, it is simpler and dynamic. It requires though that you have Power Pivot.

• First step is to change your input data so it is in a tabular format, see sheet Input. I used Power Query to unpivot the existing data table. To this data input table you need to add a column listing the name of employee for the respective team and month.
• Next step is to create a Pivot Table. As the values we want to show is text we need also to add this data to the Data Model, so that we can create a calculated field that is called a Measure.
  The formula in this measure is =CONCATENATEX(Tabell1_2;Tabell1_2[Value]). All it does is to allow us to show text as values.

As the example shows, I have changed team member for Team G and Team X3 from September to December and it is listed in the Pivot Table.

I know it is a different approach than your intended, but in my opinion this makes things easier.

Br,
Anders