Automatically update data in another workbook

[elianaelia]

Please for your help!

I have two worksheets, one is the source and the second one is the destination. In the source workbook I have a list of project names. The project names are listed in column A . Adjacent to column A, are in columns the months, where the employees will manually add the percentage of hours they worked for each project for that month.

I managed with power query to link the data in the destination file so when I add a new project in the source workbook automatically is added in the correct row of the column A in the destination workbook. However, when a new project is added in the destination workbook, creates only a cell and not the whole row. I do not want to link the whole table (this would have added a new row), I want the new row to be empty and ready to be filled by a user.

How can I automatically create a new row with filled the column A and empty the rest columns of the row when a new project is added in the source workbook?

Many thanks in advance!

 

[Matt Allington]

I don’t know how you are linking the files. I suggest you use power query to load data from source to destination and unpivot the data at the same time. Benefits include that the query can be built to work regardless off the number of columns or rows. Do you know how to do that?

 

[elianaelia]

I don’t know how you are linking the files. I suggest you use power query to load data from source to destination and unpivot the data at the same time. Benefits include that the query can be built to work regardless off the number of columns or rows. Do you know how to do that?

Even though I have successfully managed to automatically update the destination file when new project is entered in the source file, the problem is that the column with the project names moves down one cell, but the data in the adjacent rows does not (I need a new row to be created when a new project is entered).

For instance in the below screenshot we see the destination file when the employee has entered some values for ProjectName1 and ProjectName2:

Then I entered a new project name in the source file with the name ProjectName1a by creating a new row.

In the destination file the new project name appears in correct order but the data in the adjacent columns is getting mixed up since the hours entered for the ProjectName2 appear now as hours for the ProjectName1a.

The desired outcome should have been like below:

At your disposal for any further information.

Many thanks in advance!

 

[Matt Allington]

Then something else is going on that I have not seen as yet. in the first image you posted. Where does the data in green come from? Is it from the source file? If so, I have no idea why you are getting this behaviour. If it is from the destination file, then you could try this technique I wrote about here. Self Referencing Tables in Power Query

If you do follow this article, be very careful as it is very sensitive. You must have a unique primary key.

 

[elianaelia]

The data in green is data entered by the user. Ιt is not from the source file. The source file contains only the list with the projects. That list I want when it is updated to automatically be updated to the files I linked with (called destination files). When I do that, In the destination file the new project name appears in correct order but the data in the adjacent columns is getting mixed up since the hours entered for the ProjectName2 appear now as hours for the ProjectName1a (please see pictures above).

Am I clear now?