Is there a way to use a Pivot Table as the data source for an Excel Table? I need to use the data generated from a Pivot Table, but not in the order in which the Pivot Table can generate it and also with calculations that are easy in a table (subtract column A-B) but not in the Pivot (A is a Row Label and B is a Value). It needs to be a dynamically expanding area, which is why I'm looking to use an Excel Table.
Pivot Table as data source for an Excel Table in the same spreadsheet?
- Thread starter jpdavey76
- Start date
I had a similar experience lately where I need to refresh the pivot table for a list of manager names (original data sheet just won't work because I need to find out how many different managers and managers' names appear repeatedly for various staff on the data sheet across a wide range of department and regional offices, so I have to use pivot table).
But I added a helper column on the pivot worksheet (column A with index 1, 2, 3...80) and a couple more helper columns on the output worksheet to do what I need on the result. Without seeing what data jpdavey76 is working on, that's my solution. If jpdavey76 can provide a sample with dummy data and tell us what s/he needs on the output, I think we will have a better insight on how best to help out?
But I added a helper column on the pivot worksheet (column A with index 1, 2, 3...80) and a couple more helper columns on the output worksheet to do what I need on the result. Without seeing what data jpdavey76 is working on, that's my solution. If jpdavey76 can provide a sample with dummy data and tell us what s/he needs on the output, I think we will have a better insight on how best to help out?
@Luke M: Can you base an Excel Table on another Excel table and still have it dynamically expand to show only the number of rows needed to hold the data? That could possibly also solve my problem if I can do some aggregates during the pull and skip the Pivot entirely. I would pull it straight from the databases, but I need it so they can manually adjust information in the detail table and all the other Excel and Pivot Tables update based on that.
@fred:
Below is the data, where ComparePrice and Price come from different databases, one only available as a summary and the other as detail:
Data Table:
[pre]
Item Color Size ComparePrice Price
-----------------------------------------------
Apple Red Large 10.00 6.00
Apple Red Medium 10.00 5.00
Apple Blue Large 10.00 10.00
Apple Pink Medium 14.00 9.00
Apple Pink Small 14.00 6.00
Orange Orange Large 8.00 8.00
Orange Silver Large 10.00 9.00
[....]
Pivot Table output:
[pre]
Item Size ComparePrice Total of Price
------------------------------------------------
Apple Red 10.00 11.00
Apple Blue 10.00 10.00
Apple Pink 14.00 15.00
Orange Orange 8.00 8.00
Orange Silver 10.00 9.00
[/pre]
I'd really like to be able to have some header and footer rows as regular Excel rows/cells above and below the Excel Table. There are only, say, 5 fruits to keep track of, but a variable number of colors.
Desired Output (expandable as necessary, where Database 1 Price is the Total of Price column, and Database 2 Price is the ComparePrice column. Note that columns are not in the same order as the Pivot Table):
Color Database 1 Price Database 2 Price
---------------------------------------------
*Apple* + Static Header
Red 30.00 31.00 | Dynamic Excel Table rows
Blue 10.00 10.00 | Dynamic Excel Table rows
Pink 14.00 15.00 | Dynamic Excel Table rows
*Total Apple* 54.00 56.00 - Footer
*Orange* + Static Header
Orange 8.00 8.00 | Dynamic Excel Table rows
Silver 10.00 9.00 | Dynamic Excel Table rows
*Total Orange* 18.00 17.00 - Footer
[/pre]
@fred:
Below is the data, where ComparePrice and Price come from different databases, one only available as a summary and the other as detail:
Data Table:
[pre]
Item Color Size ComparePrice Price
-----------------------------------------------
Apple Red Large 10.00 6.00
Apple Red Medium 10.00 5.00
Apple Blue Large 10.00 10.00
Apple Pink Medium 14.00 9.00
Apple Pink Small 14.00 6.00
Orange Orange Large 8.00 8.00
Orange Silver Large 10.00 9.00
[....]
Pivot Table output:
[pre]
Item Size ComparePrice Total of Price
------------------------------------------------
Apple Red 10.00 11.00
Apple Blue 10.00 10.00
Apple Pink 14.00 15.00
Orange Orange 8.00 8.00
Orange Silver 10.00 9.00
[/pre]
I'd really like to be able to have some header and footer rows as regular Excel rows/cells above and below the Excel Table. There are only, say, 5 fruits to keep track of, but a variable number of colors.
Desired Output (expandable as necessary, where Database 1 Price is the Total of Price column, and Database 2 Price is the ComparePrice column. Note that columns are not in the same order as the Pivot Table):
Color Database 1 Price Database 2 Price
---------------------------------------------
*Apple* + Static Header
Red 30.00 31.00 | Dynamic Excel Table rows
Blue 10.00 10.00 | Dynamic Excel Table rows
Pink 14.00 15.00 | Dynamic Excel Table rows
*Total Apple* 54.00 56.00 - Footer
*Orange* + Static Header
Orange 8.00 8.00 | Dynamic Excel Table rows
Silver 10.00 9.00 | Dynamic Excel Table rows
*Total Orange* 18.00 17.00 - Footer
[/pre]