Recently, I have seen a few questions around allowing stakeholders to “comment” on data that is coming from SQL Databases or Odata Feeds within Excel. This can be for a few reasons:
- The user in question might not have access to the system the data is coming from.
- The data may be amalgamated from direct sources, meaning commenting on each element may require opening multiple different systems.
Initially this may seem trivial. We can take the data from the feed in question, add it as a table in Excel, then add another Column to include comments. An example below shows taking The Task table from Project Online, with a new column at the end to allow columns.
However, we run into an issue when the data is refreshed. I will delete the Team Test Project and refresh that data:
As you can see, the comment column is not related to the row, meaning that as the data is refreshed, the comment will “shift” to an incorrect row.
We can work around this by doing the following steps:
1.Right click on and table and select “Get Table from Data/Range”
2. Copy the new Query this generates
3.Edit original query
a) add the copied lines above the “In” line in the query
b) add a comma to the end of the line above the pasted lines
c) Rename the new lines so the don’t clash with any existing lines in the query.
4.Merge your query with itself using a left outer join and using an ID field as your primary key
5.Edit your original query again, this time changing the Merge column line to your original data to the data stored in the excel table.
6.Expand the new column in the query, only checking the Comment column to be included
7.Save and close.
Now, when you go to view the comments in excel, when we remove a task line, the comment is removed alongside it.
And there you have it! This solution will retain the integrity of comments within this excel file.
