Attaching Comment to cell contents rather than cell

[El Damo]

I have a spreadsheet that is regularly upadated overwriting columns,
however I need to enter comments on the sheet referenced on the
content. The next time I update the sheet the referenced cell will
move down a row, but I need the comment to stay with the content rather
than the cell.

Any suggestions?

Many thanks

 

[Dave Peterson]

If the values that you're commenting are unique, you may want to toss the
comments and create a table in another worksheet.

Value Comment

Then use an =vlookup() formula to return the "comment" to the adjacent cell.

 

[El Damo]

Thanks for this Dave, but me being really quite thick could you
elaborate.

Ta
Damo

 

[Dave Peterson]

Say your comments go into cells with unique part numbers.

Create a table on another worksheet:

Col A Col B
----- ------------------------
Part1 This is comment1.
Part2 This is a second comment
....
PartN This is the last column.

Then use that part number to bring back the comment into an adjacent cell.

=if(iserror(vlookup(a2,sheet2!a:b,2,false)),"",vlookup(a2,sheet2!a:b,2,false))

Debra Dalgleish has some notes:
http://www.contextures.com/xlFunctions02.html (for =vlookup())

 

[Dave Peterson]

PartN This is the last COMMENT.

Say your comments go into cells with unique part numbers.

Create a table on another worksheet:

Col A Col B
----- ------------------------
Part1 This is comment1.
Part2 This is a second comment
...
PartN This is the last column.

Then use that part number to bring back the comment into an adjacent cell.

=if(iserror(vlookup(a2,sheet2!a:b,2,false)),"",vlookup(a2,sheet2!a:b,2,false))

Debra Dalgleish has some notes:
http://www.contextures.com/xlFunctions02.html (for =vlookup())