Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
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,265 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

Question has a verified solution.

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

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
I was prompted to write this article after the recent World-Wide Ransomware outbreak. For years now, System Administrators around the world have used the excuse of "Waiting a Bit" before applying Security Patch Updates. This type of reasoning to me …
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…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …

618 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