Forum Discussion
Reference Cell with notes
Is there a way to reference all data (including notes and/or comments) from a cell on one sheet to a cell on another sheet. For example, on Sheet1, Cell A1 includes data and a note. If I use something like '=Sheet1!$A$1' I get the content, but not the note or comment. Is there any way to also include the note when referencing a cell?
serious_sam13
I just created a function to retrieve the note attached to a cell:
Public Function GetNote(ThisCell As Range) As String
GetNote = ThisCell.NoteText
End FunctionThat's just not the purpose of notes or comments attached to a cell.In fact, the whole idea behind those is that they be attached to the cell (or the area) where they are, that they provide background or explanation of the data in the cell or the region of the spreadsheet. If you want them to be part of the data, then make them part of the data, using an adjacent column or two.
So I'd say if that's what you want to be able to do, re-think the design of the workflow or the data set so that those notes or comments are in fact a part of the data.
Here's an example of why one would want/need to see the notes for a cell on a cell reference.
I have my budget in Excel. I have detailed notes (comments) attached to each transaction. Each month has its own sheet.
I also have a summary sheet layed out differently, where I reference the 'total' cell of each column. We just had a very hot, dry month so my water bill spiked. In the Aug-08 sheet I commented about the reason for the spike. But when I reference that cell (='Monthly Spend (08-22)'!J$6), it does not carry that comment with it to the summary sheet. Therefore my son (who reviews my spending) cannot see the explanation as he reviews the summary sheet.
Make sense why including the comments on the referenced cell would be advantageous?
It appears that Jagodragon has given you the specific method to do what you were wanting to do. So I've learned something too in the process.
It still is the case that I'd approach the whole process of budget management differently than you do. For example, I would not have separate sheets for each month's transactions. Excel can easily take a consolidated ongoing database of transactions and break apart (analyze, in various ways) that consolidated DB into monthly or quarterly summaries, by budget category, etc. Breaking it apart "manually," as you've done, makes it easier perhaps for human review, but in some ways it interferes with Excel's many marvelous functions. If it's working for you--as it appears to be--no reason to change. Just know that there are other ways to accomplish the desired results.
