Tuesday, October 05, 2004

Using a slider to change a cell and a chart



This is a cool function that can assist you to make easy to use adjustable tables.
Using the example of a mortgage, I used the loan wizard in Excel (under New Worksheet) to create a mortgage example. (Download Sample File). I then added a graph of the closing balance.
So now we have a nice looking chart that shows the closing balance of our mortgage over time.
Lets say we want to add the ability to easily change the additional payments and see graphically what that will do to our loan timeline.

From the Forms toolbar select the scroll bar. Then drag your mouse over where you want it on your sheet while depressing the left button.
Now right click the control and select format control. Set it up as follows.
Now you can scroll up and down and as you do the additional payments changes by $10 a time, and the loan balance jumps accordingly. Clicking in the slider will cause it to page up or page down at $100 a time. Obviously you can make these settings what ever you desire for any application where you need to rapidly change 1 (or more) variable and see the results.


Posted by: Jethro | 11:11 AM |