|
Excel has all the right tools you need to build a solid,
stable quoting system that saves time and is easy to use for Excel-challenged
employees. This multipart series will show you how.
This is the third installment of our four-part series
about using Excel to build a quoting system. So far, we’ve set up the basic
worksheet for our hypothetical company, AxeBuilders,
and typed in and protected our standard data, then we
covered creating drop-down list boxes to dynamically select list data. This
installment, we will cover using check boxes to dynamically select options.
Creating a Data Input Sheet: Using Check Boxes
Both the Combo Box and the Check Box features in Excel
allow users to dynamically change worksheet contents without having to key
in new data. With regard to lists, Combo Box enables users to select a single
record from among multiple records, while Check Box enables users to select
and deselect multiple records. For our purposes, Combo Box is used to select
which instrument we want to quote and what color the instrument will be made
in, and Check Boxes will be used to select which options are to be included
in the quote.
In the previous lessons, we selected the B4 bass guitar
in Red, as shown below:
Now, we will add the Options list to the Data Input sheet
so that we can allow the user to make the option selections. To do this,
in cell A4 type the “=” sign, then click on the Options worksheet tab, then
click on cell A1 and hit Enter. This will tie the content of cell A4 on the
Data Input sheet to the contents of cell A1 on the Options sheet. Then, click
drag the small black solid square in the bottom right-hand corner of cell
A4 on the Data Input sheet to include cells A4-A-6, as shown below:
This will automatically include the rest of the data from
the Options sheet. Now, we can create the check boxes that will enable us
to select and deselect the options. First, activate the Control Toolbox toolbar
if it is not currently active. A nice shortcut to doing this is by right-clicking
on an empty space of the Menu Bar, and then checking the Control Toolbox
toolbar. With the Control Toolbox toolbar active, click on the Check Box
icon, as shown below:
Then, draw a box to include the border of cell B4, as
shown below:
Now, we will set the Properties of the Check Box so that
it can do what we want it to do, namely include the Active Electronics option
in the quote. First, right-click on the Check Box that
you just drew. The Properties window will appear, as shown below:
We want to change two fields: set the Caption field to “Active
Electronics” and the LinkedCell field to “C4”.
Close the Properties window, exit Design Mode (by clicking on the leftmost
button on the Control Toolbox toolbar, the icon with the pencil, triangle
and ruler), and test the check box. Click it on and off and watch the contents
of cell C4 alternate between TRUE and FALSE, as shown below:
Then, let’s repeat the process for the Gloss Finish and Fretboard Inlays options. The final result will appear as
below, with all options deselected:
That’s it! Now that we have created a Data Input sheet
that can dynamically select instruments, colors and options, we can create
a dazzling estimate page so that salespeople can quickly and accurately quote
the company’s products. See you next time!
Download for this lesson: AxeBuilders
Quote System, Part III.zip [14k]
|
