Gathering & Inserting Comment Text

[runswick]

Hi

Please could you tell me if possible and how to gather cell text from one spreadsheet and add it to corresponding cells comment popup in another spreadsheet.

The source of the comments is changelog.xlsx
The comments are in dashboard.xlsx

The attached zip has a mock up of what im trying to do in two files.

Any help much appreciated, ive been searching for a solution for ages!

Regards Runswick

[Jakobshavn]

For demo reasons, I have implemented your request with two sheets within a single workbook. Open the attached workbook and try the macro.

[AlphaFrog]

Another method is to have a second sheet in Dashbord.xlsm and call it say Comments. It would be identical to the Dashboard sheet except the formulas link to column C instead of column B in the Changelog workbook. Then run the macro below (simular to Jakobshavn's) to update the comments on the Dashboard sheet. You could hide the Comments worksheet if you wanted.

Sub Set_Comments()
    
    Dim rng As Range, cell As Range
    
    Set rng = ThisWorkbook.Sheets("Dashboard").UsedRange.SpecialCells(xlCellTypeFormulas)
    
    rng.ClearComments
    For Each cell In rng
        If Sheets("Comments").Range(cell.Address) <> 0 Then
            cell.AddComment
            cell.Comment.Text Sheets("Comments").Range(cell.Address).Value
        End If
    Next cell
    
End Sub

[runswick]

Thanks for your help, the referencing of the source cells in the spreadsheets may not be easy to do, in reality I have about 80 spreadsheets.

All identical structurally but for different projects (places), each place spreadsheet has tabs linked to a trial, in each trial there are questions asked and comments recorded, the output from the question is a drop down and the comment is always in the cell to the right of the drop down.

I was beginning to think this was too complicated but perhaps a hidden column with an explicit reference to the comment cell?

Thanks again, I hope its clear what ive described.