Hi Friends,
I have a question on Pivot tables, please can anyone let me know if it is possible to add a formula to pivot table.
I have almost 6 pivots looking at the same data source but picking up information based on month and week.
I would like to know if it is possible to enter a formula in pivot to pickup this information (that is the month) from another sheet and update the pivots as per this items.
Thanks
HI muheeb,
Can you show a sample file along with your expected results ?
Regards,
DILIPandey
<click on below * if this helps>
DILIPandey, Excel rMVP
+919810929744 (India), +971528225509 (Dubai), [email protected]
Hi Dilip,
Thanks again for pitching in.
The attached is the sample file i have two pivots in the sheet, i need these pivots to pick up the month from a different source.
Example i have month in sheet 3, is it possible for the pivot to take the month from sheet 3 and update the and refresh the pivot table.
Not sure what is your ultimate goal is.. but you can extract month from sheet 3 in sheet 1 and then you are already using sheet 1 as pivot data source - so all set
Regards,
DILIPandey
<click on below * if this helps>
hi Dilip,
I work on a citrix environment hence not able to send the actual file.and its huge as well. so i tried to create a sample file for you.
What i am doing here is i am trying to create a control tab where the pivots are picking up information from source data and validating the entries.
SO i have these pivots on a separate sheet say sheet 3 and i have data from Jan to Dec in the source file.
I want these pivots to check the month which is being used in sheet1 and pick it up by itself in sheet 3 and refresh the pivots as per that month... hope this helps.
please tell me if i can do this.
Last edited by muheebrahman; 05-03-2013 at 08:57 AM.
I found that Sheet 1 has month as March in column A.. now you want this month to appear in B2 of sheet 3 and based on this month, pivots on sheet 4 should be filtered.. correct ?I want these pivots to check the month which is being used in sheet1 and pick it up by itself in sheet 3 and refresh the pivots as per that month... hope this helps.
Regards,
DILIPandey
<click on below * if this helps>
Okay.. as a option try the below excel workbook.
Pivot(1).xlsm
Regards,
DILIPandey
<click on below * if this helps>
Ah Sorry, I failed to make you understand at first.
WHat i need is the pivots in sheet 4 need to pick up month from sheet 3.
I have working sheet where the month is the criteria and all the data is picked up as per the month.
So i want the pivots to also use the month which is in sheet 3 and update and refresh accordingly.
Hope this sheds some light on..
sorry.. I am not able to understand.
I can assume few things here but don't like to
Regards,
DILIPandey
<click on below * if this helps>
Hi,
I tried to put my workings on a word to explain it to you.
please tell me this helps.Example.docxExample.docx
Okay .. see my above post #7 where two pivots are picking up month from a single source.
i.e, two pivots on sheet 4 are getting filtered using b2 of sheet 3.
Regards,
DILIPandey
<click on below * if this helps>
Sorry Dilip,
I still fail to understand how did u make the pivots pick up the month from different sheet.
I want the pivots to pick up the month from sheet 3.
the pivot table should select which month i am using in sheet 3 to be picked up and update the pivots with that particular months data.
Last edited by muheebrahman; 05-04-2013 at 03:15 AM.
I used vba code to do this.
Open my attachment to see the code. thanks.
Regards,
DILIPandey
<click on below * if this helps>
Yup saw that now,
But trust me i am a noob when it comes to VBA i dont know a thing about it.
Is it possible for me to copy the codes and use it in my file?
Yes.. you can use that code in your file.
See the code and wherever you find any reference to sheet / range /cell etc, change that to suit your file's data arrangement.
Regards,
DILIPandey
<click on below * if this helps>
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks