Goal Seek Tool
A simple tool that allows the user to tie the "To value" input to a cell.
One of my frustrations with Excel's Goal Seeking feature is that you need to hand enter the "To value" input as opposed to giving you the option to tie the value directly to a value in a cell (see the screenshot below).
Recently I created a simple tool which allows the user to get around this limitation. Screenshots of the tool appear below. Note the two option buttons that appear on the bottom right-side of the tool.
In the first state shown below, the user can tie a cell value to the to the second input box. In this mode a RefEdit control is used to capture the cell value.
In the second state, the tool works very similar to Excel's built-in feature. In this mode an input box is used to capture the cell value.
Several buttons appear at the bottom of the tool. After entering the three required inputs, hit the Apply button to start the goal seeking process. If the tool can't find an answer quickly, you can use the Stop button to halt the procedure. After the tool has returned a result, the Undo button can be used to put the values back to their original state.
The tool is currently designed to work with Excel 2003 and has not been tested by anyone other than myself. It can be used as-is or converted to an add-in. If you would like the file, feel free to download it via the link below.
You can make the RefEdit do double duty. Not only can it be used to select a range, it can also be used for entry of a value. The Equal To frame isn’t even needed. You just need your form validation routines to be a bit smarter.