• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1317
  • Last Modified:

How to Pull "Cell" Data from Excel Sheet in Power Pivot to Another Excel Power Pivot Sheet?

I have a Dashboard built using Power Pivot "Pivot Table" data. This "Dashboard" is really just an Excel Sheet that is displayed as a Summary Sheet with certain cells showing the Stop Light info of Red, Yellow and Green. So this is not a true Dashboard that is generated by Power Pivot but rather a Spreadsheet that pulls in data from other Pivot Tables.


Dashboard #1 (1 Excel Sumary Sheet of Data)
10 Pivot Tables
20 Datasources

Dashboard #2 (1 Excel Sumary Sheet of Data)
13 Pivot Tables
25 Datasources

Dashboard #3 (1 Excel Sumary Sheet of Data)
15 Pivot Tables
27 Datasources

1 Single Dashboard (Excel Sumary Sheet of Data)

Get data from Dashboard #s 1-3 to consolidate and compare numbers from all 3 Dashboards into 1 single Dashboard.

QUESTION: What is the best approach to creating the New Requirement of the Single Dashboard using the other 3 Dashboard Data as its Datasource?

Can I use a "VLOOKUP" or "GETPIVOTDATA" kind of approach?

  • 2
  • 2
1 Solution
Jerry PaladinoCommented:
Use GETPIVOTDATA to extract data from a Pivot Table.  
Use VLOOKUP, SUMIF, SUMIFS, COUNTIF, LOOKUP, SUMPRODUCT, INDEX & Match, etc... to pull data from an Excel List or Excel Table (List of data in rows and columns)
MIKESoftware Solutions ConsultantAuthor Commented:
I'm not as experienced in POWER PIVOT as other software.

So, I have different "projects" for each Dashboard in Excel. Do I move all Dashboard "project sheets" into a single Excel Workbook,....OR....?

Another concern is optimization of the project so that it doesn't run slow etc...

Also, the final solution will need to be consumed via Sharepoint....

Thanks for the input.
Jerry PaladinoCommented:
Based on your last post..  "I think" you have separate Excel WorkBooks for each of the three dashboards.   Based on that assumption...

No, you do not need to move all the dashboards into the same WorkBook.   You can leave them in their separate files and create a fourth workbook that will be the overall Summary dashboard.    Use the GETPIVOTDATA formulas to pull "cell" level data from the Pivot Tables on the three dashboards into the fourth(overall).

As an example, I opened two WorkBooks with Pivot tables and then opened a new workbook.   In the new workbook, I selected a cell and pressed the equal sign (=).   Then, with Excel still in edit mode, I moved to one of the other workbooks and clicked on a "Value" in one of the pivot tables.   That generated the first formula below and gave me the AOV (Avg Order Value) for the pivot data displayed.  I did the same again but went to the other file with a pivot table and that generated the second formula below.   That one gave me the dollar value for the "Category" of Food with "SalesOrigin" of Web.

Just give it a try.   It looks overwhelming at first but after you do a few of them it will become more clear.

=GETPIVOTDATA(" AOV",'[SS - Basic Cohort Info from Email Addrs-v1.0.xlsm]Pivot Report'!$A$5)

=GETPIVOTDATA("LineAmount",'[SS Email Win Control Group-v1.0.xlsm]Sales by Origin'!$A$4,"CategoryName","Food","SalesOrigin","Web")

As far as performance, with this method, each dashboard will run and update on its own within SharePoint and the final dashboard will display the updated values AFTER the other three have updated.   I am familiar with SharePoint but not a SharePoint user so I can't advise on the process within that environment.

MIKESoftware Solutions ConsultantAuthor Commented:
Nice work Jerry, much, much appreciated!  Cheers!
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now