?
Solved

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

Posted on 2013-12-08
4
Medium Priority
?
1,281 Views
Last Modified: 2013-12-09
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.

Example:

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

NEW REQUIREMENT:
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?

Thanks
0
Comment
Question by:MIKE
  • 2
  • 2
4 Comments
 
LVL 16

Expert Comment

by:Jerry Paladino
ID: 39705785
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)
0
 
LVL 17

Author Comment

by:MIKE
ID: 39706243
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.
0
 
LVL 16

Accepted Solution

by:
Jerry Paladino earned 2000 total points
ID: 39706415
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.

HTH
Jerry
0
 
LVL 17

Author Closing Comment

by:MIKE
ID: 39706460
Nice work Jerry, much, much appreciated!  Cheers!
0

Featured Post

Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you need to forecast numbers -- typically for finance -- the Windows and Mac versions of Excel 2016 have a basket of tools to get the job done.
Quickbooks hosting can do wonders to your enterprise but considering the points elaborated in the article which will help you to better analyze the outcomes. So scan your business, its needs and then move to the new world of limitless benefits.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question