Okay, so I have a table with labels and numbers
One column is labeled Product
The other is labeled Count
I want to add a column where it displays the percentage right beside the Count and refreshes wherever I add a new product or when an existing product is sold.
Bring up my post
You get better help if you add an excel-file, without confidential information.
Please also add the desired result.
Notice my main language is not English.
I appreciate it, if you reply on my solution.
If you are satisfied with the solution, please mark the question solved.
You can add reputation by clicking on the star * add reputation.
Here's a sample.
SampleBook1.xlsx
Put this in F4 and fill down.It does this.Formula:
Please Login or Register to view this content.
Row\Col Product Name Qty Sold A Row Labels Sum of Qty Sold Desired Result B A Qty Sold/Grand Total in Percetage that updates wit hthe Pivot table C B D C E D F E F Grand Total
This, unfortunately does not update with the table. I changed the table data and refreshed the table. Here's what happened: Oh and also, I forgot to mention this: the values will be sorted
SampleBook2.xlsx
Last edited by Volle12; 02-24-2015 at 12:48 AM.
I don't understand. It updates when the pivot table is refreshed at my end. What am I missing?
What's missing at my end is that if the order changes, the formula doesn't change properly and I can't quite figure out why. Is there a way to add a column to a pivot table?
What are the specific steps you take to refresh? I keep trying to duplicate problem you
describe and can't. Perhaps I am missing the point entirely.
Last edited by FlameRetired; 02-24-2015 at 01:19 PM.
Found it.
The formula used in the upload post #6 should be =$E3/SUM($E$3:$E$11)
What it has is =$E3/SUM($E$3:$E$8) which I had used in the original
upload.
Does this fix it?
Last edited by FlameRetired; 02-24-2015 at 01:29 PM.
I've added a percentage column to the pivot table. Does the attached do what you want?Originally Posted by Volle12
Last edited by FlameRetired; 02-24-2015 at 02:26 PM. Reason: clarity / afterthoughts
Thanks but when I put it in F4 it didn't "merge" with the pivot table. I tried it on a different table, it doesn't work for me.
Capture.PNG
Last edited by Volle12; 02-25-2015 at 08:57 AM.
I don't understand. In the last upload I worked the percentages out next to the source ...... in column C.
Then I changed the source data for the pivot table from the Sheet1!$A:$B that you had to Sheet1!$A:$C.
This added the column in the pivot table......which is what I understood you to want in post #8. F4 is in that pivot table. It is blank....because the source references whole columns not just the used range.
By-the-way I cannot read the attached picture. Many of us can't.....company firewalls and other limitations.
That said would you re-upload a WB that describes what your pic shows? If there is a way that you can
hand type results and point to what you want and where within that WB it might clarify.
Last edited by FlameRetired; 02-25-2015 at 01:12 PM.
I changed the source data and it worked. Thanks a lot
Glad to hear it works. Thank you for the feedback.
Hi guys,
I really like Pivots and after reading this post very fast it looks like it is in need of three things.
1. A Dynamic Named Range of data for the Pivot that grows when you add data.
2. The Pivot data is based off the Dynamic Named Range instead of a fixed range.
3. An event macro that will automatically refresh the Pivot Table if any cells are changed.
Find the attached where I've included all 3 things above. See it work by adding data to the table. It also works if you change any of the numbers in the table.
One test is worth a thousand opinions.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks