• 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.

Dynamic Dashboard

D

DSP Varma

Member
My company asked me to automate the report
In the following link, an excel file with the raw data is found in Raw Data Tab and Rawdata for payfort calculation Tab
The expected result is in KPI Data Tab
I did all the work manually with pivots and formulas.
The raw data will change every day. So I was asked to make this report dynamically so that if I change raw data the every thing should also change.
Can anybody help me out and get a dynamic result?

https://drive.google.com/file/d/0B_Z-Dgh7rj9kM3dDS1RSXy1JZms/view?usp=sharing
 
Hi ,

Your raw data consists of 5493 rows and 86 columns !

Do you seriously expect any one to go through the data , the various pivot tables and other calculations you have done , and then see how this can be automated ?

If you can explain exactly one thing that you would like automated , and explain how that automation would work i.e. what inputs would change and how the outputs would reflect these input changes , someone might be willing to help out.

Narayan
 
NARAYANK991 said:
Hi ,

Your raw data consists of 5493 rows and 86 columns !

Do you seriously expect any one to go through the data , the various pivot tables and other calculations you have done , and then see how this can be automated ?

If you can explain exactly one thing that you would like automated , and explain how that automation would work i.e. what inputs would change and how the outputs would reflect these input changes , someone might be willing to help out.

Narayan
Click to expand...

Yes, I understand the problem.

Since every thing is formulated and linked, If the pivot table changes as per the raw data my formula should change accordingly. Since the formula results is linked to the end result my job will be done.

For eg. If on one day, one pivot have 20 rows. The next day the same pivot may have 25 rows or 16 rows. So, I want the calculations and links to be dynamic.
 
Hello DSP Varma

Try this if you think your 80% job can be completed by refreshing all the pivot tables in the excel workbook.

Just assign below macro to a button...or you can also put this code in workbook open event so that when ever workbook is opened all pivots will refreshed for you..Let me know any challenges...

Code: 
Sub RefreshAll()
Dim pvt As PivotTable
dim sh as Worksheet

for each sh in Worksheets
  For Each pvt In sh.PivotTables
    pvt.RefreshTable
  Next pvt
next sh

End Sub
 
Monty said:
Hello DSP Varma

Try this if you think your 80% job can be completed by refreshing all the pivot tables in the excel workbook.

Just assign below macro to a button...or you can also put this code in workbook open event so that when ever workbook is opened all pivots will refreshed for you..Let me know any challenges...

Code: 
Sub RefreshAll()
Dim pvt As PivotTable
dim sh as Worksheet

for each sh in Worksheets
  For Each pvt In sh.PivotTables
    pvt.RefreshTable
  Next pvt
next sh

End Sub
Click to expand...


Dear Monty, for your response. will the data source change dynamically as per raw data. In the give workbook the number of rows were 5493. sometimes the number of rows may be more or less. Actually I want a code where all the pivot tables take the source data dynamically. for eg. if the rows are 5243 then 5243. If on the next day the rows increased to 6500 then the pivot tables should take 6500 rows as the source data and they should be refreshed. Thank you in advance
 
Hello Varma.

Yes you can make your pivots dynamic.

Go to Formulas Tab – Defined Names Group- Define Name

In the New Name dialog box, call the named range something, I have called this range “my_data”.

In the Refers To section is where we enter the OFFSET Formula, which returns a reference to a range specified by number of rows and columns.


=OFFSET(Rawdata!$A$1,0,0,COUNTA((Rawdata!!$A:$A),4)

Last argument in the formula 4 refers to number of columns..so mention as per your size of columns.

Now go to your Each pivot table and refer the name as My_Data rather then range.

Now irrespective of your number of rows your pivot tables will pick up

Hope this helps.
 
You must log in or register to reply here.
Back
Top