1

I'm looking up to a tab of data that has data ranging back for a couple of years. Much of the data also has notes attached to the cells.

In my summary tab, which will be looking up a months worth of daily data, I need the notes attached to the source cells to also pull through when using the INDEX MATCH functions.

I know this is not standard behaviour for INDEX MATCH, and that a user defined function in VBA might be able to get this to work, but to be frank my knowledge of VBA is 2 parts of sod all.

Is this something that is possible to achieve? How would one go about making this magic occur?

I've not tried anything because I know it's not possible with standard INDEX MATCH functions, so am at a bit of a loss as it will be necessary for this new report. I'd like to avoid having to add specific cells for commentary as it would make the summary unwieldy.

Many thanks in advance for your time.

Kind regards,

Luke

Doom
  • 13
  • 2
  • 1
    I think you would need VBA to do this. [this could be a good starting point](https://stackoverflow.com/questions/68285039/extract-notes-and-comments-of-a-cell-sheet-using-excel-vba) – cybernetic.nomad Jun 23 '23 at 14:55

1 Answers1

2

EDIT: this will pull the cell content and the comment from the referenced cell, and replicate them on the calling cell. I'm a little surprised this works with a UDF, since typically those are quite restricted in what they can do to the Excel environment, but I guess in this case it works...

Function GetNotes(v, matchRange, notesColLetter As String) As String
    Dim m, c As Range
    m = Application.Match(v, matchRange, 0) 'check for match
    If Not IsError(m) Then                  'got match
        With matchRange.Cells(m).EntireRow.Columns(notesColLetter) 'comments cell...
            GetNotes = .Value
            Set c = Application.ThisCell 'the cell with the formula
            If Not c.Comment Is Nothing Then c.Comment.Delete
            If Not .Comment Is Nothing Then c.AddComment Text:=.Comment.Text
        End With
    End If
End Function

Usage:

=GetNotes("blah",A:A,"C")

looks up "blah" in colA and returns the value and any note in colC on the matched row.

Tim Williams
  • 154,628
  • 8
  • 97
  • 125
  • Hi Tim, thanks very much for your assistance! I would need the function to return both the cell contents and the note, do you have any idea how I would be able to facilitate this? – Doom Jun 26 '23 at 10:55
  • See edits above. – Tim Williams Jun 26 '23 at 15:21
  • That's brilliant, works perfectly to pull the information simultaneously, thank you so much! Related question and possibly showing my ignorance here; is it possible to replicate the notes functionality in the same way in the destination cell that it appears at source (i.e. in a hover over text box)? – Doom Jun 27 '23 at 14:05
  • See edits above. Please remember to mark as "Accepted" if this solves your problem. – Tim Williams Jun 27 '23 at 15:46
  • Amazing, exactly what I need! This is really appreciated, thank you so much for your time and knowledge – Doom Jun 27 '23 at 15:53
  • Apologies, a follow up query if you have the time. The lookup works fine and pulls through both the cell contents, and the notes from the source cell. However if I remove the source note, or target the lookup at a cell with a different note, or without a note, the note in the destination cell doesn't get removed or updated. Is there a workaround for this, or would it be a case of batch deleting notes when the month being looked up changes? Thank you again for your help. – Doom Jun 27 '23 at 16:16
  • See edits - the new version starts out by removing any comment on the cell with the formula. The only proviso is that adding, editing or deleting comments doesn't trigger a recalculation, so you need to keep that in mind. – Tim Williams Jun 27 '23 at 20:44
  • This is perfect, thank you once again, you've made this report possible =) – Doom Jun 29 '23 at 08:16