Use value from Named Range in Comment?

[njs27]

Is this possible?

I actually have a comment that's currently in a cell (the actual text of the comment is a cell value at the moment), but I would like to use it as a comment for multiple cells. I saw somewhere that you can't do it without vba unless you use a named range. So, I named the range "SourceData_Hrs" but can't figure out how to format the comment so that the text of that cell shows up as the comment rather than the name of the range.

I hope that makes sense. My intern was capturing the source data of each wkst in a cell to the right of the data. Well, you can't see that when you first open the worksheet, so unless you know to look there for it, you could be staring at the wkst wondering where this all came from.

I tried "SourceData_Hrs", @SourceData_Hrs, @[SourceData_Hrs], "[@SourceData_Hrs]", =SourceData_Hrs, and =@SourceData_Hrs

Not sure what to try next.

TIA!

[Aardigspook]

Using a named range in a comment just means that the comment will still be valid if you insert/delete rows/columns/cells - that is, if you have a comment 'This is the sum of B13 and C13', it would need to be updated if you insert a row/column, but 'This is the sum of Sales_Jan and Sales_Feb' wouldn't.

Without VBA there isn't a way to insert information into a comment automatically.

Is there a reason you can't put a helper column beside the cells containing the information instead of a comment?
If you don't want to do that and there is a formula in the cell, you could use the 'N' function to add information which would be visible in the formula bar - but it wouldn't appear on the sheet itself.

Please Login or Register  to view this content.

↑ This works because N returns 0 (zero) if given a text string, so adding it at the end doesn't affect the rest of the formula (B2+AC2 in this case), but is visible to anyone using the file if they click on the cell.

I don't know if that's of any help?

[njs27]

Thanks for replying!
I think I understand now about the named ranges.

Yes, I basically want to add a comment to every column in the spreadsheet so that I (and everyone else on the team) will know where that data came from. The cells I want to use the comments in have text in them, not a formula. I wonder if I could do something like
=N("text that's currently in the cell")+N(comment)?

I'll give it a try.

I'm comfortable with VBA, but didn't want everyone who has to open it to deal with enabling macros, etc.But maybe I can just run the macro and then save it as a non-macro file.