Dynamic Notes?

[PrettyGood_Not Great]

Is is possible to have a note, dynamically populated? Could one perform a lookup with a cell reference and the result appear in a note?

 

[Jeffrey Mahoney]

In my opinion. Notes cannot have formulas updated with the rest of the sheet. You can update notes using VBA. You could update them manually triggered or automatically by:
* Checking a cell or range change
* Sheet Activate or deactivate
* Selection change
* and others

 

[PrettyGood_Not Great]

In my opinion. Notes cannot have formulas updated with the rest of the sheet. You can update notes using VBA. You could update them manually triggered or automatically by:
* Checking a cell or range change
* Sheet Activate or deactivate
* Selection change
* and others

I am not a strong VBA user, could you provide an example? I have a lookup table on a seperate sheet. The cells in question that are to contain the note/comment is the lookup value. The comment itself would be the return from the lookup table.

Ex. If this were an XLOOKUP.....=XLOOKUP(cell containing comment, lookup array, return array).

 

[Jeffrey Mahoney]

Here goes. Ask many questions if needed. I created a button to update the Notes in a range of cells I called NotesRange. In each Note you need to add a line that looks like a formula that references another cell with the formula that gives you the results you want to display in the Note. In the Note for F7 on row 7 I put =O7. I put this on row 7 because I didn't want to show that when the user puts the mouse over the cell. The macro looks at each cell in the NotesRange and then finds the line where there is a Equals sign and interprets that as the cell you want to get. The macro replaces line 1 with the results of that cell and puts the rest of the lines back in also.

Create a button and call it UpdateNotes_Btn. Add the code below to the sheet module that the button resides

Book2
	E	F	G	H	I	J	K	L	M	N	O
1								Col1	Col2
2								1	$15,784.12
3								2	$21,589.54
4								3	$58,213.45
5								4	$10,478.23
6
7		4									$10,478.23
8
Sheet1

Cell Formulas
Range	Formula
O7	O7	=XLOOKUP(F7,L2:L5,M2:M5,"",0)

Named Ranges
Name	Refers To	Cells
NotesRange	=Sheet1!$F$7:$F$11	O7

Add this code to the SHEET Module

Private Sub UpdateNotes_Btn_Click()
  Dim Cel As Range
  Dim aStr As String
  Dim LNS As Variant
  Dim X As Long
  Dim fStr As Variant
  
  For Each Cel In Range("NotesRange")
    aStr = ""
    fStr = ""
    On Error Resume Next
    aStr = Cel.Comment.Text
    On Error GoTo 0
    If aStr <> "" Then
      LNS = Split(aStr, Chr(10))
      For X = 0 To UBound(LNS)
        If Left(LNS(X), 1) = "=" Then
          fStr = Range(Mid(LNS(X), 2, 100)).Value
          'Debug.Print Cel.Comment.Shape.TextFrame.Characters(12, 1).Font.ColorIndex
          Exit For
        End If
      Next X
      If fStr <> "" Then
        LNS(0) = fStr
        aStr = ""
        For X = 0 To UBound(LNS)
          aStr = aStr & LNS(X)
          If X < UBound(LNS) Then aStr = aStr & Chr(10)
        Next X
        Cel.Comment.Delete
        Cel.AddComment
        Cel.Comment.Text Text:=aStr
      End If
    End If
  Next Cel
      
End Sub

 

[PrettyGood_Not Great]

Here goes. Ask many questions if needed. I created a button to update the Notes in a range of cells I called NotesRange. In each Note you need to add a line that looks like a formula that references another cell with the formula that gives you the results you want to display in the Note. In the Note for F7 on row 7 I put =O7. I put this on row 7 because I didn't want to show that when the user puts the mouse over the cell. The macro looks at each cell in the NotesRange and then finds the line where there is a Equals sign and interprets that as the cell you want to get. The macro replaces line 1 with the results of that cell and puts the rest of the lines back in also.

Create a button and call it UpdateNotes_Btn. Add the code below to the sheet module that the button resides

Book2
	E	F	G	H	I	J	K	L	M	N	O
1								Col1	Col2
2								1	$15,784.12
3								2	$21,589.54
4								3	$58,213.45
5								4	$10,478.23
6
7		4									$10,478.23
8
Sheet1

Cell Formulas
Range	Formula
O7	O7	=XLOOKUP(F7,L2:L5,M2:M5,"",0)

Named Ranges
Name	Refers To	Cells
NotesRange	=Sheet1!$F$7:$F$11	O7

View attachment 119620

Add this code to the SHEET Module

Private Sub UpdateNotes_Btn_Click()
  Dim Cel As Range
  Dim aStr As String
  Dim LNS As Variant
  Dim X As Long
  Dim fStr As Variant
 
  For Each Cel In Range("NotesRange")
    aStr = ""
    fStr = ""
    On Error Resume Next
    aStr = Cel.Comment.Text
    On Error GoTo 0
    If aStr <> "" Then
      LNS = Split(aStr, Chr(10))
      For X = 0 To UBound(LNS)
        If Left(LNS(X), 1) = "=" Then
          fStr = Range(Mid(LNS(X), 2, 100)).Value
          'Debug.Print Cel.Comment.Shape.TextFrame.Characters(12, 1).Font.ColorIndex
          Exit For
        End If
      Next X
      If fStr <> "" Then
        LNS(0) = fStr
        aStr = ""
        For X = 0 To UBound(LNS)
          aStr = aStr & LNS(X)
          If X < UBound(LNS) Then aStr = aStr & Chr(10)
        Next X
        Cel.Comment.Delete
        Cel.AddComment
        Cel.Comment.Text Text:=aStr
      End If
    End If
  Next Cel
   
End Sub

Thanks for this. I can see now that I was over complicating the matter. Because my lookup data is a header row generated by the same lookup table of interest, all of my values will always be in the correctly aligned order. The mini sheet below shows the problem I need to solve. Cell D7 should have a comment displaying "10", E7 with a comment displaying "11" and so on. Could you suggest a simplified solution to this?

The header row in practive is a TOROW(named range), with the lookup table on a separate sheet.

Book1
	A	B	C	D	E	F	G	H	I	J	K	L
1	1	10
2	2	11
3	3	12
4	4	13
5	5	14
6	6	15
7	7	16		1	2	3	4	5	6	7	8
8	8	17
9
10
Sheet1

 

[Jeffrey Mahoney]

I need further explanation. Are you still on track trying to get Notes updated automatically? I don't see the issue on the mini sheet.

 

[PrettyGood_Not Great]

I need further explanation. Are you still on track trying to get Notes updated automatically? I don't see the issue on the mini sheet.

The notes could be updated via button as you suggested, what's changed now is that I no longer need to perform an equation based lookup, at least not based on my original intention. Would I still use the VBA approach you've shown as is?

 

[PrettyGood_Not Great]

The notes could be updated via button as you suggested, what's changed now is that I no longer need to perform an equation based lookup, at least not based on my original intention. Would I still use the VBA approach you've shown as is?

let me try another mini sheet

 

[PrettyGood_Not Great]

let me try another mini sheet

Book1
	A	B	C	D	E	F	G	H	I	J	K
1	Header	comments		1	2	3	4	5	6	7	8
2	1	10
3	2	11
4	3	12
5	4	13
6	5	14
7	6	15
8	7	16
9	8	17
10
Sheet1

Cell Formulas
Range	Formula
D1:K1	D1	=TOROW(lookup)
Dynamic array formulas.

Named Ranges
Name	Refers To	Cells
lookup	=Sheet1!$A$2:$A$9	D1

 

[Jeffrey Mahoney]

Let me put it this way, if you want to update a Note automatically, VBA is the only way