Hello! Here's a quandary I've been mulling over at work. I need to create a .xls that will:

  • Connect to the org financial database, which is not editable and cannot be added to. Not a problem, as I have database access all set up.
  • Display month-by-month revenue in a pivot table, with customer name as rows, and months as columns. Also easy, this took only a few minutes to do. Importantly, each row of customer names is unique.
  • Have an extra editable column directly next to the pivot table, so management can add comments on revenue trends for each customer account. Done, but...

Here's the issue: this pivot table must be refreshed weekly. As the table is sorted by total revenue, the customer names reorder every time I refresh. That means a comment written in row 20, for the customer previously in row 20, now appears like it's a comment for the new customer sorted into row 20.

For reasons outside my control, I can't fix this from a database level. Any way to allow users to both add comments AND keep those comments tied to each customer name, regardless of customer sort order? I could use PowerBI if needed - but I doubt that would solve the issue better than Excel, in this case.

Bonus problem: if possible, it would be amazing if I could automatically save the pivot table data each week, then compare it to the last few weeks' data - thus showing trends over time. Is that possible - to have Excel "save" the old pivot table data before the refresh, and categorize that old data somewhere with a date dimension added? Hopefully this all makes sense - I would show pictures, but confidentiality prevents that. Let me know if building an example file might help, or if this is explanation enough!

Thank you very much in advance for any ideas!