Forum Discussion
Comparing data in columns on 2 separate worksheets
Jeri_info4u I would create on list and add an extra column containing the source of the data. Then you can create a pivot table and summarise everything in one pivot table. The attached file contains such a "quick-and-dirty" pivot table.
- Riny_van_Eekelen Thank you for the response! I was able to create the pivot table and get the desired results using your method. There's one exception. How did you get the calculations in red on the Smartsheets data to produce? My Pivot Table looks just like yours minus the red calculations.
Jeri_info4u Omitted to mention the variance columns. I added another value field for "Evaluation Date" and set it to 'Show Value As' difference from Raw.
That will produce some extra columns with pretty ugly and long headers, which I just replace with a single space. In the Raw group, the variances will obviously be blank (note I reduced the column width considerably), but in the SmartSheets group you will get some variances. Custom formatted these with 0;-0; (this will suppress any zeroes) and colored them red to make them stand out.
- Riny_van_Eekelen Thanks again for your response! I must not be doing something right. I am doing it in such a way that it matches your screenshot but I keep getting the error message that I cannot enter a null value as an item or field name in a pivot table report. I am not sure where to put the 0;-0; or how to add the variance as you did.
