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,098 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
Nice work Jerry, much, much appreciated!  Cheers!
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
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.

772 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

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now