• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Request for help linking pivot table to data sheet.....

D

Deepa9

Member
Hi,

I have one report where there are many pivot tables in different tabs and In 2 sheets I have data or i can say matrics. every month I need to update 2 data sheets where I have all matrics from the pivots.

Problem:
1. To update all numbers from pivots it takes around 2 hours to copy paste 60 matrics.

requirement:
1. Can you please help me to automate this with a macro which should match the field name and matrics name then take numbers every month after refreshing pivots.
or else
2. Can you please help me with some formula which can go to that perticular field name and take numbers.

This is one report where i am spending more hours. please help me with this.

Thanks in advance
 
Hi Deepa ,

I think a workbook with the full data layout is a must if you want help on this.

The workbook should have the correct field names ; the data itself is immaterial.

Narayan
 
Hi,

Thanks for your reply.

I can't share the full data as it has some confidential information. I am attaching a sample file with only one pivot and with one small data sheet. If you tell me how to do this I can apply the same thing to the file. please help me with this. Thanks in advance

In the attached file there are two tabs first tab is data sheet, and second tab has pivot from which i copy paste the numbers. please help me to with some formula or automation.
 

Attachments

  • DUMP.xlsx
    27.6 KB · Views: 8
Hi,

I believe you can try GetPivotdata() formula. whenever you do refresh PT, it will automatically update your data sheet.

In case you need to retain the previous PT's numbers, once you get the data using formula mentioned above, copy and paste as values, so that whenever you do refresh of PT, it would not change the values in data sheet.

Regards,
Prasad DN
 
Hi Narayan,

I want to link pivot to the datasheet so that if i do a refresh every time the formula should pick the same value. please help me

Hi Prasad,
I have tried using Get pivot data but somehow i am not so comfortable in using that formula.

Thanks in advance
 
Hi,

The problem i am facing in get pivot data is if i copy formula from one cell to another its taking same number.

eg: if i copy get pivot data formula from Jan to feb it will take jan number. plz help me to solve this.
 
Hi Deepa ,

I suggest you do not try to do what you are thinking of doing viz. try and extract values from a pivot table.

It is far better to directly operate on the source data and derive what ever measures you want.

A pivot table can have different layouts , and may be filtered / viewed in different ways. Taking all of this into account using formulae , is difficult.

Narayan
 
Hi Narayan,

Thanks for your suggestion, I will try adding Get pivot formula, let see how it will work. if not then I will do the samething that is copy and paste.

Thanks
 
Deepa9 said:
Hi,

The problem i am facing in get pivot data is if i copy formula from one cell to another its taking same number.

eg: if i copy get pivot data formula from Jan to feb it will take jan number. plz help me to solve this.
Click to expand...


Hi Deepa9,

In reference to your above quote, if you see the formula it creates when you use getpivotdata, you can see if it referring to Jan (like absolute reference), you need to edit it to Feb manually, or you instead simply give the cell address like relative reference.

Again, as Narayan said, the PT layout/filters and views may affect your result. You need to keep the PT layout consistent.

Regards,
Prasad DN
 
Hi Prasad,
Thanks, can you please give a Getpivot formula with relative reference so that if i copy it to next cell i should get feb numbers.
Regards,
Deepa9
 
Hi,
Thanks, hope this works. I have one question when i refresh the pivot a new month will be added to it is that the data remains the same.
 
Hi Deepa:
It will work if you drag the cell reference in row 1 and the formula in row 3, once new columns get added in your pivot. You can do it now as well if you can wrap the getpivot formula with an iferror statement.
 
You must log in or register to reply here.
Back
Top