0

I have tried to optimize the outcome of a Pivot table by using the Solver to modify the inputdata of the Pivottable. It seems to me this is not possible because the Pivottable is not updating automatically after altering the inputdata. So if the Solver starts changing the inputvalues, nothing happens to the outputvalues which the Solver tries to optimze. I also read this post which says - if I understand correctly - it is indeed not possible:

Can I use solver on pivot tables?

If this is not possible, I was wondering if it would be possible to use the solver in combination with Power Pivot tables? In that case I would start a course on Power Pivot.

Thanks. Marcel

Community
  • 1
  • 1
Marcel
  • 115
  • 2
  • 15

2 Answers2

0

I don't have enough reputation to comment so I will answer (isn't that backwards?) PowerPivot probably won't let Solver do more than Pivot tables did. I'd say try to reproduce the layout and looks of your pivot with sumproducts, sumifs, etc. Add binary variables for each field you were using in a filter/row/column. Then optimize that with Solver.

kindoflost
  • 91
  • 1
  • 1
  • 7
0

It's actually possible to use Solver with Pivot Tables as long as you use a little bit of Visual Basic. I found a part of the solution in this page: https://www.get-digital-help.com/2011/06/13/auto-refresh-a-pivot-table-in-excel/

Basically, setup a macro in your Pivot Table sheet to automatically update it on any data change. This can be done with this code (update the pivot table name and worksheet name).

Private Sub Worksheet_Change(ByVal Target As Range)
    Worksheets("Pivot table").PivotTables("PivotTable1").PivotCache.Refresh
End Sub

Then, make sure all of your Solver variables are in the same worksheet as your pivot table and you should be good to go now.

JcMaco
  • 1,258
  • 3
  • 16
  • 31