Interactive comments box

[Dunkertons]

Hi,

I have created a sales dashboard for some product managers.
At the top are 2 drop down boxes (the 2nd being dependent on the first, thanks to another thread on here! ) whereby the user can select the manager and then select one of the product categories for which that manager is responsible.
All the sales figures then update according to those selections, which is great.

However the challenge now is that those managers want to be able to add some short narrative/comments against each product group, in the form of a text/comments box which is static and visible on the dashboard page. They want to be able to free type the comments into the box and for those comments to then be stored against that product group, so that the next time that product group is selected in the drop down, the comments appear again in the text box.

I've looked at using a pivot table on a separate tab with a macro to open that tab and filter the pivot table for the appropriate manager as selected on the dashboard drop down - the idea being you can enter a comment in the cell next to the relevant product group and a vlookup in a hidden/off-screen cell then enables you to populate that comment back into the text box (i.e. text box contains formula "=that hidden cell"). However entering the comments in a cell next to the pivot doesn't work as the comments remain static when the pivot filter changes....

Is there a step to solve this, or is there a completely different way to achieve what is I guess an "interactive and dynamic comments box", using VBA or otherwise?

I'm using Excel 2010 by the way, many thanks

Sam

 

[Dunkertons]

p.s. I forgot to mention that the source data is stored in a table, and each product group has multiple rows of data. I.e. not possible to enter the comments per product group back into the source data as there are 1000's rows, and the comments should apply to groupings of these rows.