Automatically update a list of data from one workbook to another

[elianaelia]

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?

For instance in the below screenshot we see the destination file where 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:

Hope I was clear enough!

Many thanks in advance!

[Anonymous]

Hi @elianaelia 

You can add a custom column

e.g 

if [SourceEPRO]=[DestinationEPRO] then [1-lav] else null

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[elianaelia]

Where shall I add the custom column? In the source file next to the column A that contains the list of projects? Thanks a lot for your response.

[Anonymous]

Hi @elianaelia 

Can you show how do you link the data in the destination table with source file?

Best Regards!

Yolo Zhu

[elianaelia]

Yes of course. I can sent you the files. How can I PM you

@Anonymous