Adding data to a table filled with data from a different Excel file

[2meter]

Hi all,

To keep track of my work for several projects, I made an Excel sheet. I fill the sheet with data by a query from a different Excel file (which is managed by someone else) and the data is capured in a Table. I then apply some filters to sort out the projects I need.

I added additional columns to the same Table in which I write my remarks on the status of a project. This works fine as long as in the source file new projects are added to the bottom of the table. However, when new projects are inserted somewhere in the table of the source file, my remarks get tagged to a different project as it does not seem to have a connection with the same project number.

Each project has a unique number which is in column A. Is there a way I can couple my remarks to the project numbers so that they keep connected after refreshing the data?

I hope my problem is clear and thanks for your help!

[Richard Buttrey]

Hi and welcome to the forum.

One way would be to take a temporary copy of your Project number and Comments column, or even the whole table on a separate sheet BEFORE you invoke a Data query update.

Then after the update use an Xlookup to refresh your comments column from the temporary copy, the delete the temporary data.

[2meter]

Hi Richard,

Thanks for your reply and warm welcome

Your solution looks interesting but manually copy-pasting the table every time I need to update the table (sometimes multiple times a day) is a bit laborious. I do like the Xlookup part though. So what I did was to make a new table containing my comments and use Xlookup to populate the table with information like client, project description and employee who worked on the project. The table Xlookup is referring to is automatically filled with a query.

Not to miss new projects, I added an additional column to the table that is filled with the query in which a COUNTIFS is uesd to check if each entry is already in my comments table. Finally, I made a small table below my comments table that uses the FILTER function with multiple arguments to give a list with the projects that are missing in my comments table. Although I could not find a way to automatically add new projects to my comments table, this will work just fine.

[Richard Buttrey]

Glad you came to a solution. Incidentally I wasn't suggesting that you do all the copy and pasting to a temporary table manually. A simple macro would do that.