Theory problem: connecting comment column to database-connected pivot table

efire_

New Member
Joined
Aug 8, 2014
Messages
6
Office Version
  1. 365
Platform
  1. Windows
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 accounts as rows, and months as columns. Also easy, this took only a few minutes to do.
  • 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 accounts 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 account, 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!
 

Some videos you may like

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.
L

Legacy 456155

Guest
Any way to allow users to both add comments AND keep those comments tied to each customer account, regardless of customer sort order?
Is there any column or combination of columns in your pivot table that uniquely identifies each row? If so, then yes.

it would be amazing if I could automatically save the pivot table data each week
Of course you can. My further input here depends on the answer to the first question.

Have a nice weekend! :)
 

efire_

New Member
Joined
Aug 8, 2014
Messages
6
Office Version
  1. 365
Platform
  1. Windows
Thank you for the quick reply! Yes - the Customer Account Names are all unique per row. Example table below - unique customers in column 1, then each week of revenue in the rest of the columns.

Customer Account Name9/25/20 Revenue10/2/20 Revenue10/7/20 November Revenue
Customer X$100$110$120
Customer Y$50$300
Customer Z$200$220$100
 
L

Legacy 456155

Guest
Let me know if building an example file might help

Yes it would help. If you don't have file-sharing available, let me know.
 

Watch MrExcel Video

Forum statistics

Threads
1,114,485
Messages
5,548,333
Members
410,828
Latest member
A9Bosv3
Top