Combining Data from Pivot Tables and Sheets
Hello,
Here's the backstory I am creating a dashboard complete with dynamic views, reports, sheets and pivot tables.
On my main data sheet I have a column labled Supervisor Name and another labled Department. In this main sheet I calculate numbers that correspond with an employee name for each month. I ran a pivot table and sorted it by department and I take the sum of each month's numbers which relate to an employee name. The pivot table is great, it gives me almost everything that I need but I would like to create another column on the pivot table and create a % of a total sum, add Supervisor names and departments associated with the employees on the pivot table. When I add columns to the pivot table, my pivot table does not update. I was unsure how to do a vlookup for Supervisor name and department so I manually did the vlookup in an Excel file and copy and pasted this data into the pivot table. What is the best way for capturing data from the main data sheet and also from the pivot table? I want to run reports off of the pivot table with cumulative total data to then run dynamic views. I update the main data sheet monthly.
What am I doing wrong?
Thanks!
Answers
-
Hello!
I would recommend using a combination of the INDEX and MATCH Function, https://community.smartsheet.com/discussion/84774/index-and-match-across-two-sheets-a-detailed-explanation
In this case,
For the "range" of the INDEX formula, you'll need to create a sheet reference to your main sheet and select the column header for "Supervisor Name", making sure that the whole column is selected. https://help.smartsheet.com/articles/2482644-create-cross-sheet-references
The "row_index" portion will be the MATCH formula
The "Employee Name" on your Pivot Sheet will be the "search_value" of the match function
You'll create a sheet reference to your main sheet and select the column header for "Employee Name", making sure that the whole column is selected
The "search_type" is optional - https://help.smartsheet.com/function/match
The formula should look something like this when complete: =INDEX({Supervisor Name},MATCH([Employee Name]@row, {Employee Name},0))
You can then turn this into a column formula on the Pivot sheet, which will in turn pull "live" data for that column from your main sheet as long as the "Employee Name" exists on both sheets
This process can be repeated for the "Department" column.
To your point of "pivot table does not update" please double check your "Execution Frequency" in your pivot settings. The recommended setting is at least an hour or longer. We've noticed that "immediately" doesn't happen instantaneously, but rather gets added to a queue so it may take some time. https://community.smartsheet.com/discussion/94522/pivot-app-not-automatically-updating
Hope this helps!
Davin Vo - Sevan Technology
Smartsheet Platinum Partner
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.5K Get Help
- 430 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 146 Just for fun
- 63 Community Job Board
- 466 Show & Tell
- 32 Member Spotlight
- 2 SmartStories
- 301 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!