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

efire_

New Member
Joined
Aug 8, 2014
Messages
7
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!
 

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.
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! :)
 
Upvote 0
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
 
Upvote 0
Hi, was there ever a resolution to this ? I am doing something very similar where I refresh pivot data daily but I want a comment to stay with the data unless it drops off the pivot table

Thanks all
 
Upvote 0
Hi, was there ever a resolution to this ? I am doing something very similar where I refresh pivot data daily but I want a comment to stay with the data unless it drops off the pivot table

Thanks all

I actually just recently found what MIGHT solve the issue: self-referencing queries. Still don't understand it fully, but youtube search "power query self referencing" and you'll get a few hits.
 
Upvote 0

Forum statistics

Threads
1,213,487
Messages
6,113,943
Members
448,534
Latest member
benefuexx

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top