Other sheets can be made from these seven base sheets. This is done by edited the information on the relevant sheet, and then “save as” another name to keep both sheets i. e. monthly review… once January is completed, that sheet is saved as “Jan stats”, then the info is deleted off the sheet then figures are entered for February then that is saved as “Feb stats”. The same can be done for the generic order form and yearly review. Main menu sheet The purpose of this sheet is simply that is it a directory for the user.

From any other sheet in the workbook, with a click of a button (which is on every sheet) the user can go back to the main menu and chose from the buttons which sheet they want to go to. With another click of a button, the user is then taken to the sheet that they selected on the main menu sheet. The purpose of this is so that there is little confusion as possible for the user, as when looking through so many figures, stats and information, the user could easily get confused with what sheet they want an where it is.

It works by using six macros to get to other sheets and another six to get back to the main menu. Buttons that go to the sheet as stated on the button In the screen shot above, it shows the worksheet working perfectly. With this sheet, there were no problems at all creating it and the macros involved. In this sheet, there are five macros. These were made by pressing the record macro button, while starting on this page, while recording I clicked on to another sheet and then stopped recording the macro.

After the macro had finished recording, I created a button, which I assigned the macro to and renamed the button after the sheet that it clicked on i. e. the monthly review sheet. This macro takes the user back to the main menu from any sheet. This is how it is shown is Visual Basic. This screenshot shows in visual basic the macro that takes the user to the customer database from the main menu sheet. . This screenshot shows the macro that takes the user from the main menu to the discount table Screenshot of the macro opened in visual basic that takes the user from the main menu to the monthly review.

This is a screenshot of the Visual Basic macro, which takes the user from the main menu to the order form sheet. This screenshot shows the macro that takes the user to the yearly review sheet from the main menu. This screenshot shows the macro that transfers the user between the monthly review and yearly review. The macro being assigned to the button along with the recording and playback macro buttons in the top left hand corner Customer database sheet The purpose of this sheet is so that the user can keep a record of all the customers that have purchased products from the firm.

It holds all their relevant contact details such as names, address, contact number, email and the amount paid by the client and date on which the products were bought. This sheet can also be used to create a mail merge file in word; this can be used for sending out mass newsletters and information to all clients with their own personal details on the letter. Also another purpose of this sheet is so that the user can add the amount paid and date of custom to the list so that it can be reviewed and analysed in the monthly and yearly review sheets.

All of which can either be manually typed in by the user or by copying and pasting the information into the relevant sheets. This screenshot above shows how using a macro will not allow the user to enter a new client on to the database. One way of resolving this issue is that the user can type the data in the spreadsheet under the column headings and under the other customers. Another way is for the user to use vlookup, but a problem with this is that it only works for one entry and when you want another entered, it did not work.

It just changed the previous clients’ information that was entered using vlookup. The solution is to insert a new line above the new entry. This allows there to be a space in the sheet for the next new client. This is because when creating the macro the information was pasted into row 14, and with the inserted line above the data transferred, it allowed the future data to be pasted into row 14 again. This will work every time when entering transferring the data from the invoice sheet. The screenshot shows the macro used to add new customers straight to the database.

With a click of a button, all the relevant information is automatically transferred. This is done by recording a macro then copying and pasting the information to the relevant cells on the correspondent sheet, then inserting a new line for the next entry. The macro is then stopped and with the “send to database button” the information circled in a red box, is transferred to the customer database. The purpose of this is so the user does not need to copy and paste or manually type the information in. The next shot shows the sheet working perfectly.

There is one macro used in it, it allows the user to go back to the main menu directory and therefore click on to any other sheet. It also shows the added entry from the macro that transfers the client’s information with the click of a button. Other functions which are on this sheet is that it includes an auto filter which allows the headings to be sorted into a custom way and also the user can pick one client from it and only there details appear. The auto filter is on every heading so the information can be viewed and customised from other headings.

Furthermore, cells in the A, B column have been formatted so only letters can be inputted, as it is the name. Column H has been formatted for only numbers, J has been formatted so only dates can be in those cells and K is formatted for currency, as it is the amount paid. This screenshot shows the sheet working with the auto filter, the links that click on to an email message already with clients email address and the macro button that directs the user back to the main menu. Also in row 15, it shows the customer which comes from the order form.

Product Information sheet The purpose of this sheet is that it provides the user with the information of the products. It includes a unique 4-digit code that the user specified in the user’s needs. Along with the code, there is a description of the product, the price and the quantity remaining. The sheet allows the user to keep track of what products the company sell, as all of the products available would be listed. Moreover, after each order the user manually changes the quantity remaining which makes sure the quantity remaining is always checked.

The worksheet works by four main column headings: product code; product name; price and quantity remaining. All work together to provide the necessary information that the user would require. Screenshot showing the validation working for when the quantity falls below 3 Validation is used so the user is reminded with a message. This works by data ; validation and then setting rules for the sheet to follow, so if the value becomes lower than 3 (as with the conditional formatting) a message appears telling the user to re-order stock and asking if the user wants to continue.

If the user presses yes, then the conditional formatting comes into play. This screenshot shows the conditional formatting used in this sheet. The conditional formatting highlights the quantity remaining column when the stocks run below three. In following screenshots, it will show the quantity above three and the cell is not highlighted because it is not below three, which is what I have set the formatting to do. To do this I went to format ; conditional formatting and from there I set the rules for when the cell is highlighted.

When the validation and conditional formatted cells (all cells under the quantity remaining heading) the cell is not highlighted and has no messages. In addition, this sheet has been protected which means that nobody except the managing director who would have the password to the workbook can access the restricted areas. In the case of this product information sheet, I have locked the price cells so that no one except the managing director can change the prices. This would make sure there is no unofficial changing of the prices.

The only way prices can be changed is to unprotect the sheet with the password. To protect the sheet you format the cells you want to protect and make sure it says they are locked, then by protecting the sheet. Screen shot showing the protecting sheet process Screenshot shows the final product information sheet with the four headings and nine example products In this sheet, it has an auto filter for the four top headings that allows the user to organise and list the products in any way they want to i. e. ascending alphabetically or numerically or with least quantity remaining.

It also has the main menu button in the top right. Moreover, the conditional formatting and validation of cells is programmed to work when a quantity remaining cell falls below three, as shown in screen shots above i. e. the message and highlighted cells. Order Form Sheet The purpose of this sheet is to act as an invoice sheet that can be printed off, sent to customers, and filed away for the firm – if they wish to. In addition, it can be “saved as” another file name so that the generic sheet can be used more than once.

The delivery charge, sub total, discount given, grand total and the products automatically appear after the product code is typed. Furthermore, the information from this sheet can be transferred to the customer database by clicking the “Send to database” button. This sheet works out everything for the user and the main purpose is to save time for the user as once the formulas have been programmed the totals and so fourth will be correct as long as the requirements do not change. This screenshot shows all the formulas displayed. The vlookup feature formula is linked to the product information sheet.

The table used in the product information sheet allow the user to type in the product code in the A column and with the vlookup formula the price of the product would appear. With the price appearing, I used that cell to multiply with the quantity to work out a subtotal. The subtotal is then multiplied with a discount rate to work out another total. The discount is worked out by using another table (discount table), with same process as vlookup, it allows a figure to be entered and the discount automatically appears. Other formulas like the delivery charge are a standard i?? 10 with additional i??

1 per mile, which is worked out automatically through another formula in cell D28. The installation is manually entered, if the client wishes. The total adds up the sub total with discount, delivery and installation and then the final price. The final price needs to be entered again in cell D32 as this allows the total to be transferred to the customer database along with the other personal information. The formulas work out all the relevant calculations (totals, delivery etc) which the user would have done manually before the system, the system eradicates any human error from occurring when calculating totals.

This sheet also includes a drop down menu (right) of card payments, so the user would not need to keep typing out the card names instead they use this drop down list. The final order sheet has all the space for clients personal information, payment information, all totals automatically calculated and can all be sent to the database for further use i. e. mail merged letter. The following is the extremely long macro that transfers the information from the order form to the customer database. The following is the processes that I went through to produce the macro.