Goal Seek With Multiple Input Cells

I am wanting to goal seek in one "set cell" by changing the values in multiple cells. For example, A1:H1 are my range of cells with formulas that I want to change and J1 (with a formula) is my set cell that I am wanting to goal seek.

Two additional requirements are that there can be no duplicate values in the range A1:H1 and that the goal value will sometimes change, so I would like to reference the goal value from a another cell, say cell L1.

I am just starting to learn how to use VBA codes created by other people and have tried to modify a couple of codes I have found that do similar functions, but have had no luck in making them work. I am hoping this is not an overly complicated code to create. Any help would be greatly appreciated.

Originally Posted by MrShorty

It is difficult to say for certain without actually seeing the problem, but it sounds like the built in Solver utility should be able to solve the problem for you without VBA.

I'll Take another look at the Solver. I didn't get anywhere when I previously tried to use it. I would post my actual worksheet, but it is huge and complicated. The formulas are daisy chained through multiple links. It has crashed my Excel a few times because of it.

Thanks for the input.

I have retried the Solver add-in and it does almost work, but I still can't figure out how to prevent duplicates. How do you prevent duplicates with the constraints?

Originally Posted by MrShorty

It will depend on the details of your Solver model. I could see trying a =COUNTIF() function somewhere in the model, then set a constraint that this COUNTIF() should be <=1. I could also see that not working very well.

Here is how I solved it. Which probably only applies to my specific problem, but I will post it any way. I used this formula in my range of input cells:

=IFERROR(INDEX($A$808:$AF$808,SMALL(IF(COUNTIF($LB807:$LQ807,$A$808:$AF$808)=0,COLUMN($A:$AF),""),COLUMNS($A:A))),"")

Since the values I get are within a specific range (1-16), I used this formula to index from values that are not in the range (17-32). Anyway, it works for me and prevents duplicates. Thanks for the help.

but I still can't figure out how to prevent duplicates

After you have define the range of cells to change you add the constraints so you could selct the "dif" setting i.e. this ensures that ever cell in the range specified is different to each other.

How good this is? Don't know as I never have used it. Probably best not to specify to many cell as "dif"

Alf

Originally Posted by Alf

After you have define the range of cells to change you add the constraints so you could selct the "dif" setting i.e. this ensures that ever cell in the range specified is different to each other.

How good this is? Don't know as I never have used it. Probably best not to specify to many cell as "dif"

Alf

I tried it, I still got duplicates. There must be additional constraints needed? I couldn't figure it out

I figured out the the Solver add-in was not giving duplicates. I did not select the Integer constraint, so I was getting fractional returns. Where I seen two 6's, I was actually getting 6.1 & 6.4, but my cells were formatted to show whole numbers only. The Solver works great, it can even do two separate ranges as input values. I'm glad I took a second look at this tool.