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

Dashboard doesnt refresh when I hit pivot refresh

R

Rinki

Member
Hi,


I have almost prepared the whole dashboard ( 11 tabs ) basis my 1 data source and everything is basis pivot table – like all my reports and charts, etc.


When I added few rows in the data source assuming my dashboard will refresh the numbers- it went blank.


Strangely I am not understanding and I need help in figuring out a way whereby on daily basis if I change the data( add rows, my dashboard should just refresh the numbers. )
 
Possible ideas:
Go to you PivotTable, and verify that the Data Source range is defined correctly?
Is the raw data stored in a XL table, or just regularly listed?
If not a table, does it use a dynamic range? If yes, is range defined correctly?
 
Is the source on different workbook than the dashboard?

If so, you can't make dynamic range or table as source for Pivots (you can make it a source, but it will not refresh with source update).

You will either need to use PowerQuery along with PowerPivot, or Code VBA to update Pivot data range by checking source workbook.
 
As long as the source data is in same workbook convert the source data from Range to Table and change data range for all pivot to table name.

That will make it dynamic.
 
If you set your data up as a TABLE (highlight your data, including header row, go to INSERT then TABLE and name your table DATA or something you will remember. Point each of the Pivot Tables to the DATA TABLE and it will update autmatically from now on.
 
Can you please share the process how to dot it exactly.

I can share the file if you want to have a look at it.
 
Highlight your data range.
upload_2016-4-19_10-5-2.png

Click Ribbon tool "Format as Table" and choose any style you want.

Ensure "My table has headers" is checked.

upload_2016-4-19_10-6-35.png

Click anywhere on the table and go to Table Tools. Top left you will see "Table Name:", change it to something like tblData (I usually prefix with tbl to quickly search & list tables only).

Goto PivotTable tools->"Change Data Source" and type in the table name.

See attached.
 

Attachments

  • SourceData.xls
    26.5 KB · Views: 1
If the above doesn't work, sharing the file may help us arrive at a solution faster.
 
Strip it down to only necessary info to demonstrate your issue. Or use third party file upload service.
 
But I want to know where to go and do this action :

Goto PivotTable tools->"Change Data Source" and type in the table name.

Like i did it in 1 of my pivots- i dont get this option at all- it says ref invalid.
 
Go step by step. If the ref is Invalid. It means you didn't first convert your data to table and name it.

Go to your data sheet and follow steps outlined.
 
Hmm? You just type in the name of the table (ex. tblData).

Remember, it's not the PivotTable you are naming but the source data.
 
Hi,

I just tried adding few records of data in the source data to refresh the Sales Rep Perf tab...... again the whole data turned out to be empty.

so i am still at square one.

My data is real time, dynamic and changes daily- hence the need to set up pivot table in a way where i just have to refresh all in workbook and all my reports/charts would get updated.
 
Rinki said:
Hi,

I just tried adding few records of data in the source data to refresh the Sales Rep Perf tab...... again the whole data turned out to be empty.

so i am still at square one.

My data is real time, dynamic and changes daily- hence the need to set up pivot table in a way where i just have to refresh all in workbook and all my reports/charts would get updated.
Click to expand...
Hi ,

Can you upload your file after you added a few records ?

Narayan
 
I could finish do my dashboard finally the way i wanted to refresh.

I dont know but yesterday was a bad day but today seems to be good.

thx team for helping me.
 
You must log in or register to reply here.
Back
Top