Solved

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

Posted on 2013-12-08
4
1,186 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 500 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

Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

Question has a verified solution.

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

Suggested Solutions

Outlook Free & Paid Tools
Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

726 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