HI
I am looking to link a workbook to another. But I need to be able to read the comments from the original workbook in the new one. Also I'd like to have the formats displayed too.
Any suggestions
HI
I am looking to link a workbook to another. But I need to be able to read the comments from the original workbook in the new one. Also I'd like to have the formats displayed too.
Any suggestions
Hi there!
You can't do this directly. I mean, you can copy/paste the cells and the comments will transfer but there will be no linking.
A workaround to this is to output the comments from the first workbook into a column and then link to that column.
Or, you can simply use a custom formula to point to the cells that have comments in the first workbook and output them in the second workbook.
Either way, the comments will end up being output into the spreadsheet itself in the second workbook. The only workaround is VBA, which is probably overkill and prone to errors when it comes to working with specified external workbooks, though it's still possible.
The easiest solution could look like this:
Install this UDF into your workbook where you want the data to appear:
Function showComment(cell As Range) As String
' Cause an empty cell to appear instead of an error.
On Error Resume Next
' Outputs the comment from the selected cell, if it exists.
showComment = cell.Comment.Text
End Function
Just hit Alt + F11 and in the VBA window go to Insert > Module and paste the code into there.
Then, go back to the worksheet and type the formula showComment( into the cell where you want the comment to appear and navigate to the worksheet where the comments appear and select the first cell in the column and hit enter.
Now, you can copy this formula down the column in the destination workbook and it will show any comments that it finds from the selected cells in the source workbook.
This may seem a bit confusing but give it a shot and let me know how it goes!