Combining Data from Pivot Tables and Sheets

Smartsheets823

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!

Davin Vo

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!