Dynamic Filtering with Solver

[kangajohn]

Hi All,

I'm new here but have been using excelforum for a while, you guys have been a great help.


I want to use Excel Solver to solve MAX(Sum()) by iterating a variable cell from 1 to 4. Thing is, this cell is used to filter the primary table.

E.g. If variablecell's value is changed to 4, the table will filter for values of 4, which in turn adjusts the MAX cell

Please find attached an example

Anyway you can think of getting the solver to work in this fashion (ideally without VBA)?

Cheers,
John

[Alf]

Perhaps like this? As solver is not happy with your changing K2 I've set up your model running Evolutionary solver and using M5 as the cell to change. Still it's
not the best solution as solver finds a solution almost at once but will not stop so I set a time limit of 5 seconds to get a result.

Looking at the text lower left corner of screen you see Incumbent 15000 pop up almost immediately i.e. the solution but solver keeps on testing because it's not sure this is the best
solution. When message box pops up click "Stop"

Alf

[kangajohn]

Thanks Alf. that did the trick!
I guess it isn't ideal but exactly what i was looking for appreciate your help saved me lots of hours

[Alf]

You are welcome and thanks for feedback

Alf

[astupig]

Use Array Formula
Type 1 in N5, 2 in N6...
Type formula
=SUM(IF(ISNUMBER(SEARCH(N5,"" & $D$4:$D$23)),$D$4:$D$23))
in O5, copy the formula down....