Pivot table - Usable notes section OR cells move with data

[ThomasCarter]

Hi. Currently have a pivot table containing many names and other information. We need to add a notes section so users can go into the document and add notes to come back, be read and actioned.

Currently we have the notes as regular cells right next to the pivot table, so the users do not touch the pivot table but enter information next to them. (Row 1 is Aaron Angus, Row 1 notes are for Aaron Angus. etc.)

But if Aaron Angus was removed from the list it would move everyone under Aaron up 1, meaning the notes are now not next to the right names.

I want to somehow make it so these notes follow the data in the pivot table. If a user is deleted, the data is deleted. If a user is added in the middle, all the people that move will have their notes move with them.

Thanks in advance

Edit: Reason i don't use pivot table for notes is that it groups everything together (and changes them together) and i don't want 1000 "blank" notes, or common notes like "Moved to team 1" to change every single entry once changed for 1 person. Is there a way around this?

[ThomasCarter]

Ok...

So... I've found a solution, sort of.

The process is: Notes will be added in individual worksheets, reviewed by someone, and then added to the main worksheet and actioned if necessary.

So I have added the notes section from the main worksheet into the pivot table and hidden it. Then I have used the following formula to take information from this
=IF(R6 = ("(blank)"),"",R6)
[If the pivot table is blank show nothing, if it's not blank, show the data.]

This allows the user that enters notes into the individual worksheets to see the current notes and add their own.

The inconvenience of this method is:
1). You must inform your users to delete/edit over the formula in the cell they wish to enter notes. This is very straightforward but may cause some confusion as nobody likes deleting formulas they don't know the purpose of!
2). You must replace the formula after you update the main worksheet with the notes.
To do this you must simply post the formula into the notes for the first person (always W6) and drag it down the column to effect all people on the list.


If anyone has a more convenient alternative I would love to hear it!