Microsoft Office Tutorials and References
In Depth Information
Scroll Bar Limitations
FIGURE 18.8 Scroll Bar for Price
Now you have two scroll bars, one for the number of years and one for the price.
You may want to try and change the down payment of the loan with a new scroll
bar. Try to vary it from 2,000 to 6,000 with increments of 500 and a page change of
1,000. You may discover that changing the interest rate does not work.
SCROLL BAR LIMITATIONS
The Format Control menu will only allow values that are between 0 and 30,000.
Furthermore, it will not accept any fractions of an integer, such as the interest rate. In
other words, the values for the scroll bar have to be integers from 0 to 30,000. If
you want to use fractions or percentages
values above 30,000, you will have to bypass the scroll bar limitations. How do you
For values over 30,000 you will need to control a separate/dummy cell.
I created a scroll bar on the sheet named Large Numbers. The scroll bar controls
the cell B5. For demonstration purposes, I did not change any of the values in the
Format Control menu, and used the default values, varying B5 from 0 to 100.
I used a formula in cell B6 (
as you will need in our model
) to multiply the value of B5 by 10,000.
The effect of this formula is that the values in cell B6 vary from 0 to 1,000,000 with
increments of 10,000 (1,000,000
10,000). This enables me to bypass the
30,000 limitations of the scroll bar. See Figure 18.9.
The same logic applies for using the scroll bar to vary values of fractions or
percentages. This time you should create a formula to divide the values of a con-
trolled cell instead of multiplying them.
Going back to the car loan example: Create a scroll bar next to cell C5 as shown
in Figure 5.10. As you control cell C5, you can use the formula C5/100 in cell B5,