Hello all! New to the forum and glad to be here
I currently have a pivot table I'm working off of and the data is re pulled each day. Below my pivot table I have a simple formula to add a few rows and divide =SUM(C6,C16)/C22.
I'm running into issues now that when the data is re-pulled if a new item, comes up it drops the formula and is now calculating the incorrect cell. How can I do this so it is always reading the same total line?
Welcome to the Forum AMG16!
Impossible to say without knowing how your pivot table is organized, and what cells C6, C16, and C22 are. Your probably want some kind of a VLOOKUP to retrieve values based on which ones are the totals.
Can you attach your file? The paper clip icon does not work for attachments. To attach a file, under the text box where you type your reply click the Go Advanced button. On the next screen scroll down and click on Manage Attachments, which will show a pop-up window to Select and Upload a file. Then close the window.
Jeff
| | |·| |·| |·| |·| | |:| | |·| |·|
Read the rules
Use code tags to [code]enclose your code![/code]
Thank you the reply. I have attached the file. I probably should have place the formulas on the bottom since I need it by month
Last edited by AMG16; 06-19-2019 at 10:54 AM.
There is no file attached. Please see instructions in my prior post above.
It looks like the file failed due to the size limit, it should be good to go now. Just removed some months of data.
There are two ways to do this.
1. Ignore the fact this is a pivot table, and using lookup functions to find what you need. This is for the Total Shrinkage, January.
Formula:
Please Login or Register to view this content.
2. Use Pivot Table functions to extract the data (same value)
Formula:Please Login or Register to view this content.
The second one is easy to do if you type "=" then click on the cells in the pivot table where you want them in the formula; Excel will write the GETPIVOTDATA function automatically.
Also, instead of this format
Formula:Please Login or Register to view this content.
just use
Formula:Please Login or Register to view this content.
Sorry for the late response back I have been away on vacation! Thanks for the above this definitely helped out a lot. Now I need to add something for the month. Some business groups don’t have data say for Jan and the first month is April it will pull April’s data Under Jan since it counts columns
If I understand what you want then the following formula for the monthly columns may help:Formula:Please Login or Register to view this content.
The formula for the 'Grand Total' column could read:Formula:Please Login or Register to view this content.
If that does not help then please upload a file that demonstrates the issue.
Let us know if you have any questions.
Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.
Thanks for the response. I tried to make my file smaller so I can attach it.
Example of The issue I'm having if you click workgroup new hires you can see the data didn't start until March. Since it was using the column # it placed March data under Jan.
I want it to be able to read the month as well to make that match and bring the data under the correct months going forward.
The above formulas have been modified for the file, and the monthly formulas are wrapped in IFERROR(...,"")
Let us know if you have any questions.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks