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

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().
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
7
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.
 

Fraserj1979

New Member
Joined
Mar 28, 2021
Messages
3
Office Version
  1. 365
Platform
  1. Windows
  2. Web
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
 

efire_

New Member
Joined
Aug 8, 2014
Messages
7
Office Version
  1. 365
Platform
  1. Windows
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.
 

Forum statistics

Threads
1,148,269
Messages
5,745,776
Members
423,971
Latest member
Jogesh

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
Top