Wine

Ed Uncategorised

Brook Eden Vineyard

 
 

Data Tutor 3

Ed Uncategorised

Database Tutorial - Continued...Back

(2) Forms for User Interface

Forms provide a better interface for the majority of users because;

  1. They can make it obvious what information is required
  2. You can include code to validate user input
  3. They can include complex logic to transform user input into the required data

You can also make the form look really cool. Just be careful that art does not obscure function. Most users prefer a worksheet that does something, and does it really well - they do not use worksheets because they like looking at works of art on their screen. Having said that, a good looking, clean interface is much better than a cluttered, messy layout.

 

Continue...



Data Tutor 2

Ed Uncategorised

Database Tutorial - Continued...

Now click on any cell in column B, a drop-down arrow appears in the cell and you can select from the list of customers you have entered in the Setup sheet. The Data - Validation dialog also allows you to enter custom input and error messages.

Now go to the column heading (cell B1) and use menu item Data - Validation click the "Clear All" button and then click "Ok". This ensures that you can change the heading later if you wish.

c) Lookup lists

We can select a product code using data validation in much the same way. However, it would be nice if, when selecting a product code we also brought in some related information - in our example this would be the product name, price and product type.

Go back to the Setup sheet and enter some product information - our example places column headings in B1:E1, and some data in cells below these. Then use Insert - Name - Define to add a dynamic range called "P_Code" that refers to the product code column only. The formula for the dynamic range is "=Setup!$B$2:INDEX(Setup!$B:$B, COUNTA(Setup!$B:$B))".

Add another dynamic range to refer to all the product information, call it "Product" and use the formula "=Setup!$B$2:INDEX(Setup!$E:$E, COUNTA(Setup!$B:$B))".

Go back to the data sheet, enter the column heading "Product" in cell E1, then enter the following lookup formula in cell E2 - "=VLOOKUP($D2,Simple.xls!Product,2,FALSE)". Enter the column headings "Case_Price" in cell F1, and "ProdCat" in cell G1. Then copy the lookup formula from E2 to F2 and G2, you need to change the index number to 3 and 4 respectively to lookup the correct values. Copy the formulas down to the extent of the data.

We can add a "Sale" heading in cell H1, and enter the formula "=C2*F2" in cell H2, which is simply the number of cases sold times the selling price. Copy this formula for all the data rows also.

Now create a new dynamic range that refers to the data list. Call the name "AcData", the formula we will use is "=Data!$A$1:INDEX(OFFSET(Data!$A:$A,0,COUNTA(Data!$1:$1)-1), COUNTA(Data!$A:$A))". This slightly longer formula makes the range dynamic for both rows and columns. i.e. If we later add a column to the right of our data list, the new column will automatically be included in the range "acData". Notice that the "acData" range includes column headings, this will be useful in the next section.

You now have a simple data list, where data entry is made easier through validation. Related information is added to the data list through formulas.

Select a cell in the first row (containing the headings) and use the menu item Data - Filter - Auto Filter to obtain a filtered list. By clicking the arrows on the column headings, you can now filter the list.

d) Reporting through Pivot Tables

The data list is easy to maintain, but does not in itself provide a good report layout. One of the most powerful reporting tools in Excel is the Pivot Table. (If you are not familiar with Pivot Tables, you can take the Pivot Table tutorial on this site.)

To create a Pivot Table based on the data list, use the menu item Data - Pivot Table and Pivot Chart Report. In Step 1 of the Pivot Table Wizard, select "Microsoft Office Excel List or database" and "PivotTable" (the defaults) and click "Next". In Step 2, enter "AcData" (no quotes) as the range for the Pivot table, then click Next.

In Step 3 of the Pivot Table Wizard, click the Layout button and drag fields to the table diagram as shown below.

Click "Ok" and then click "Finish". The finished Pivot Table should look something like this;

Look at our Pivot Table Tutorial if you wish to learn more about using Pivot Tables.

If you think dynamic ranges are cool, but you find it a little difficult entering all those COUNTA formulas, download our Utility add-in. This lets you build dynamic ranges just by selecting some cells.

 

Continue...



Data Tutor 1

Ed Uncategorised

Excel Database Tutorial
Part 1

This tutorial has been designed for beginning to intermediate users of Excel. In Part 1, you will learn how to construct a simple Excel database and how to use a pivot table to create reports from the database.

The second part of the tutorial (coming soon) shows how to use VBA and Forms to automate the workbooks built in Part 1. By providing a friendly user interface, you are effectively building an application that allows even novice users to use your workbooks.

The tutorial places a lot of emphasis on developing workbooks with a common style. The adopted style has been developed over a number of years so that it makes good use of Excel's underlying features and provides a good platform for workbook development. This makes it easier to maintain a large number of very different workbooks. If you know, for instance, that every workbook you develop contains basic data in a sheet named "data", uses dynamic ranges that are documented in a sheet named "docs", and contains parameters for user controls in a sheet named "setup", then you can quickly make changes to your workbook even years afeter you first developed it.

In part, this tutorial explains my style of workbook development. Hope you enjoy it.

This tutorial is for use with the Excel workbooks provided. The finished tutorial excercise is Simple.xls. Download this now if you wish to skip the tutorial and use the sample database.

If you have any comments, please e-mail me at This email address is being protected from spambots. You need JavaScript enabled to view it.

Contents

(1) Setting Up the Workbook

  1. A question of style
  2. The data list
  3. Lookup lists
  4. Reporting with Pivot Tables

(2) Forms for User Interface

  1. Determine what the form must do
  2. Adding Controls
  3. Event code
  4. Manipulating the Pivot Table through code

1) Setting Up the Workbook

a) A question of style

Default installations of Excel open new workbooks with 3 blank sheets (16 if you have Excel 95). I usually change the default settings (in Tools-Options) to open new workbooks with only one sheet. It is an easy thing to add new sheets when you need them, and I find it annoying to receive workbooks from people that contain 3 worksheets where one, two, or three might contain data, and the others are empty.

I strongly suggest that all worksheets in the workbook are renamed to indicate what they contain, and any empty sheets should be deleted. To rename a worksheet, double-click the sheet tab and type a new name.

In this tutorial, we will build some simple Excel databases. We will use four sheets, named ‘Data’, ‘Setup’, ‘Docs’, and ‘Report’. Hopefully it is fairly easy to guess what type of information we will put in each of these sheets, but I will spell it out here.

 

Data Contains only raw data. One record for each row, different fields in each column, column headings in the first row. This is a simple Excel data list.
Setup Contains any information we will need to define workbook parameters and to limit data input.
Summary Contains a report based on the information contained in the Data sheet. In this case it will contain a summary report written as a pivot table.

In practice, we could have several report sheets, each with a meaningful name. eg

b) The data list

For the purposes of this tutorial, we will set up a simple workbook to track sales of wine for a small wine wholesaler.

Create a new workbook, rename "Sheet 1" to "Data" add column headings for the date of sale, name of the customer, number of cases sold, and product code for the goods sold. Enter some data so that the sheet looks like this;

Each customer name needs to be entered in exactly the same way every time. Otherwise, you would not be able to filter the list correctly, and you would be unable to add sales for each customer.

You can always copy customer names downs the list, but a better solution is to keep a standard list of customer names and to use cell validation techniques to add a customer to your data list.

First, insert a new sheet and rename it as "Setup". Then add a list of customers as shown below;

 

Define a dynamic range for the customer list. Dynamic ranges automatically expand as you add data to cells below the range. In this case we will use a COUNTA function to count the number of non-empty cells in column A, and the INDEX function to return the last non-emty cell in column A. Then the dynamic range can be written as "startcell in column:last non-empty cell in column", or A2:Index(A:A,last non-empty cell in column), or A2:INDEX(A:A,COUNTA(A:A)). We will need to use absolute addressing to lock the start and end cell of our range.

Make sure you are in the Setup sheet, use menu item Insert - Name - Define to show the following dialog, then enter "Customer" in the Names textbox and enter "=$A$2:INDEX($A:$A,COUNTA($A:$A))" in the Refers to textbox. Click the Add button - Excel helpfully adds sheet references for you. You can see this below.

 

Now, go back to the Data sheet, select the whole of column B by clcking on the column header, and use menu item Data - Validation to set up validation for every cell in column B. In the "Allow" drop-down select "List", and in the "Source" textbox enter "=Customer" which is the dynamic range you have just defined.

 

Continue...



Charting

Ed Uncategorised

Some Charting Tools for Excel

This page demonstrates the charting features of Microsoft Excel. The use of VBA to control chart objects is explored.

This is a small collection of Excel workbooks which provide charting tips and utilities using VBA macros. All the files are provided as samples, whilst some of these are useful in their own right, they are meant to be demonstrations of VBA code which you can edit for your own use.

 

Most files are compressed using .zip. The Size column gives an indication of file size in Kb.