Populating data in one cell by selecting another cell

[jschumacher21345]

I have an excel calendar (Sunday-B1 through Monday-H1). Each day has four horizontal cells in to allow for several entries. For example, Wednesday the 1st uses E2-E5, Thursday is F2-F5. The next Wednesday would be E6-E9 and so on. In those cells, I have names of meetings. What I would like to do is be able to click on a meeting and have additional information regarding the meeting (time, attendees, read ahead information, etc.) to populate in another cell labeled "Notes". Right now that note section is K2. The purpose is to have a clean looking calendar that I can present to the boss. I believe the additional information regarding the meetings that I want to populate in the notes section would have to be either referenced from another sheet or from VBA. I've very new to VBA and I would post what I have so far, but I don't even know where to begin. Long story short, I'd like to select say cell E4 which would contain the name of a meeting on Wednesday the 1st and have supporting information for that meeting populate in cell K2. If I click on another date/meeting name, I need K2 to clear and be replaced with supporting information for the newly selected meeting. Thank you all for the help!

 

[Scott T]

You could inserting a comment work instead of putting the notes in K2.

If you want VBA then, could there be duplication of the meeting names but would pull up different notes?
Also where are the notes and how are they entered?

 

[jschumacher21345]

I thought about the notes but they want something more presentable for a large screen and to be able to edit text and color. Right now, the notes are no where. I thought about duplicating the calendar in a different sheet and putting the notes there. I just don’t know how to link it to the corresponding cells in sheet 1 and have it populate in K2.

 

[Scott T]

I assumed the notes would be on a sheet named notes

Book1
	A	B
1	meeting name	notes
2	meet1	this is the notes for the meeting
3	meet2	this is more notes m2
4	meet3	m3
5	meet4	m4
6	meet5	m5
7	meet6	m6
8	meet7	m7
9	meet8	notes 8
10	meet9	like more notes
11	meet10	note
12	meet11	another boring meeting
13	meet12	yet another meeting
notes

Right click on the tab for the sheet with the calendar and select view code and past the code below. I assumed if you click in a blank space you would want the notes cleared from K2.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Dim rng As Range
Dim ws As Worksheet
Dim lr As Long


Set ws = Sheets("notes")
lr = ws.Cells(Rows.Count, "A").End(xlUp).Row
Set rng = ws.Range("A2:A" & lr)
Range("K2").ClearContents




If Not Intersect(Target, Range("B2:H25")) Is Nothing And Target <> "" Then
    'f = Application.Match(Target, rng, 0)
    Range("K2") = Application.Index(ws.Range("B2:B" & lr), Application.Match(Target, rng, 0))
End If


End Sub

 

[Scott T]

I have added some error handling. If the meeting is not found on the notes sheet then a mesagebox will display

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Count = 1 Then
    Dim rng As Range
    Dim ws As Worksheet
    Dim lr As Long
    
    
    Set ws = Sheets("notes")
    lr = ws.Cells(Rows.Count, "A").End(xlUp).Row
    Set rng = ws.Range("A2:A" & lr)
    Range("K2").ClearContents
    
    If Not Intersect(Target, Range("B2:H25")) Is Nothing And Target <> "" Then
        'f = Application.Match(Target, rng, 0)
        Range("K2") = Application.Index(ws.Range("B2:B" & lr), Application.Match(Target, rng, 0))
        If IsError(Range("K2")) Then
            Range("K2").ClearContents
            MsgBox "meeting not found"
        End If
    End If
End If
End Sub

 

[jschumacher21345]

That is almost exactly what I need. However, My calendar (Sheet 1) is set up like the picture above. The Notes sheet (sheet 1) will be the exact same except I will be putting the meeting notes in the cell that corresponds to the meeting on the calendar sheet. I'd like to be able to click on the meeting and have the notes from sheet 2 populate in K2.

 

[Scott T]

For some reason I can not see the first picture.

 

[jschumacher21345]

Ok, hopefully this works haha. It looks like the table below. Any of the 4 cells below the cell with the date could have a meeting input. The notes sheet will be the same, but I will be adding all the notes there so it won't be as pretty and presentable. That is why I want the notes to appear in K2 on the first page when you click on the meeting name.

A______B_____C______D______E_____F______G______H
______________________________________________________
|______|30____|31____|1_____|2_____|3_____|4_____|5_____|
|______|______|______|______|______|______|______|______|
|______|______|______|______|______|______|______|______|
|______|______|______|______|______|______|______|______|
|______|______|______|______|______|______|______|______|
|______|6_____|7_____|8_____|9_____|10____|11____|12____|
|______|______|______|______|______|______|______|______|
|______|______|______|______|______|______|______|______|
|______|______|______|______|______|______|______|______|
|______|______|______|______|______|______|______|______|
|______|13____|14____|15____|16____|17____|18____|19____|
|______|______|______|______|______|______|______|______|
|______|______|______|______|______|______|______|______|
|______|______|______|______|______|______|______|______|
|______|______|______|______|______|______|______|______|
|______|20____|21____|22____|23____|24____|25____|26____|
|______|______|______|______|______|______|______|______|
|______|______|______|______|______|______|______|______|
|______|______|______|______|______|______|______|______|
|______|______|______|______|______|______|______|______|
|______|27____|28____|29____|30____|31____|______|______|
|______|______|______|______|______|______|______|______|
|______|______|______|______|______|______|______|______|
|______|______|______|______|______|______|______|______|
|______|______|______|______|______|______|______|______|

 

[Scott T]

Does this do what you want? I assume the notes will be in the same cell only on the notes sheet.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

If Target.Count = 1 Then
    Range("K2").ClearContents
    
    If Not Intersect(Target, Range("B2:H26")) Is Nothing And Target <> "" Then
        Range("K2") = Sheets("notes").Range(Target.Address)
    End If
    
End If
End Sub

 

[jschumacher21345]

That is perfect! Thanks for all the help with the noob questions.