Copilot for Microsoft 365 Tech Accelerator
Feb 28 2024 07:00 AM - Feb 29 2024 10:30 AM (PST)
Microsoft Tech Community

Reference Cell with notes

Copper Contributor

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?

7 Replies

@serious_sam13 

 

That'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.

@mathetes 

 

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?

 

 

This gives you the complete comment from cell A1

ActiveSheet.Cells(1, 1).Comment.Text

@Joekelley78 

 

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.

I agree with you. But, I have had the misfortune of having to work with people who are resistant to even a minor change in how they input data in excel. So, I've had to try and learn how to help them in spite of their best efforts to not be helped.

I assume this is a similar problem. The method you propose is BY FAR the better option.
Yes. I will do both, but whenever possible, I do my best (and often successfully--especially if the existing practice is still in the early stages) to point to more effective use of Excel's marvelous abilities to manipulate, extract, summarize...

What I find, as a general observation, is that people who are relatively new to Excel will often essentially transfer over what I'll call a paper-ledger-spreadsheet mentality. That's what often leads to monthly sheets, or business inventory tracking with separate sheets for different categories of products, etc. In all such cases, that "separate sheet" could readily be handled simply by a distinct column--or by dates alone, in the case of the monthly sheet mentality (Pivot Tables, as you no doubt know, will take a set of dates and consolidate them into monthly summaries).

@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 Function