Start Using Resolver One

Sample One - An office supplies price sheet

In this example, we create a spreadsheet that shows the products held by an imaginary office supplies company, and use Resolver One's features to make it easy to extend and update. This introduces you to three core Resolver One features:

• Defining a function and using it in the grid.
• Setting and using header rows.
• Writing simple sheet-wide conditional formatting.

The sample spreadsheet can be found in the file simple-function-and-formatting.rsl, which is in the Samples subfolder of the Resolver One folder in your Start Menu. However, by following through the steps below manually you may find you understand the structure of the sheet better, and learn how to use Resolver One more quickly.

• The first step is to enter the list of products that the company stocks, and their net prices (excluding tax). Enter the following data into a worksheet in a fresh Resolver One workbook (you will need to adjust the width of column A to make everything visible):

• Make the first row of your table bold, by selecting it (click on the "1" row header) and then clicking the "B" icon in the toolbar.

• Next, make the first row into a Header Row, by right-clicking inside it and selecting the "Set Header Row" option from the popup menu.

• Now write a function in the Pre-constants user code to calculate what the gross price would be once VAT (sales tax) has been added onto the net amount, where Gross Price = 1.175 * Net Price. Do this by clicking in the Pre-constants User Code section in the coding pane, and entering the following (note that capitalisation and spacing are significant, so make sure they match):

If you're unfamiliar with programming, don't worry too much about the precise details of this code for now. All will be explained later.

• Add a new column header in your worksheet to the left of the "Net Price" one: "Gross Price".

• Use the function you have defined to calculate the gross price of the goods in this column. So, for example, in cell C2, which should show the gross price of the paper, you would enter =withVAT(B2)

• You can simply enter this formula once, copy it, then select the range from C3 to C7, and hit paste - the formula will be copied with appropriate adjustments for each row.

• Format the numbers in both price columns so that they are listed to two decimal places. To do this, select the two columns in the grid by clicking on the "B" header, and then dragging to the "C" header, then from the "Format" menu select "Number", set the "Decimal places" option appropriately, and then click "OK".

• In the user code highlight the rows that have a gross price of over 25.00. To do this, enter the following into the Post-formulae user code, again being sure to make the spacing and capitalisation match:

• Again, the details of how this works will be explained later. For now, click back on the grid, and, you will see that the rows for Printer Ink, Table and Notepads will all be highlighted to show that their price is greater than 25.

• Add another row to the bottom:

• Copy the formula for the gross price down from the Notepads row so that you can see the appropriate Gross Price. You will see that as soon as there is a Gross price, the row is highlighted.

A later sample (Columns generated by code) shows how you can use more advanced code so that you could avoid even having to copy the gross price formula when adding more rows.

Back to Sample Spreadsheets.