Applied ict 2 by English School Los Olivos

Applied ICT PRACTICAL

A2 1

Section 15: mail merge ICT Department Year 13 Applied ICT

Section 15: Mail Merge

15.1

Introduction to mail merge â&#x20AC;&#x201C; create master document structure.

15.2

Create a source file.

15.3

Check data entry.

15.4

Set up variable fields for automatic completion.

15.5

Set up variable fields to control record selection/omission at mail merge run time.

15.6

Set up fields and prompts for manual completion

15.7

Automatically select the required records.

15.8

Use manual methods and software tools to ensure error-free accuracy.

15.9

Perform mail merge.

Year 13 Applied ICT

Section 16: Automation Section 17: Output Data

16.1

Select the most appropriate software application for a particular task.

16.2

Set up a selection facility which will allow the required documents to be selected: Design a menu. Create macros, create hyperlinks, use of VBA.

16.3

Set up a suitable selection facility which will allow the required documents to be selected and to allow the user to select the data within the documents.

17.1

Save and print the document.

Mail Merge The steps to complete a mail merge are as follows: 1. Set up the main document 2. Connect the document to a data source 3. Refine the list of recipients or items 4. Add placeholders, called mail merge fields, to the document 5. Preview and complete the merge

Defintions

Access [Databases] Using Parameter Queries Entering a Parameter Instead of typing a value or expression into the criteria cell, type some text enclosed in square brackets ([ ]). The text you type will appear as a prompt on a dialog box, so you might want it to be in the form of a question to the user. In this example the user will be prompted to type the name of a town when they run the query. The text that the user types will be used as the criteria for that particular field. The dialog box looks like thisâ&#x20AC;Ś

If the user were to type London then this query would display all the records with the entry London in the Town field. Using Multiple Parameters You can enter a parameter almost anywhere you would place a piece of text, number or date in a regular criterion. For example, supposing you wanted the query to prompt the user for two dates to define a date range...

Instead of typing the actual beginning and end dates into the criteria cell, type a prompt in square brackets. The user will see two separate dialog boxes, each asking for a date. After entering dates the query proceeds, inserting the dates into the appropriate places in the criteria expression. In this example the query would display all the record which contained dates in the range 1 November 1998 - 30 November 1998 in the Invoice Date field...

You can use as many parameters as you want, in as many fields as necessary. The dialog boxes appear in the same order as they do on the QBE grid. Combining Parameters with Wildcards… A useful feature of the query is its ability to accept wildcards (i.e. an asterisk "*" representing any string of characters; one or more question marks "?", each representing a single character). Wildcards allow you a degree of flexibility when specifying criteria. When you don't know exactly what you are looking for you can use wildcards to give the query a "clue". This method can also be applied to parameter queries, but you need to do a bit more than just add an asterisk or question mark. The correct syntax is as follows… For a single wildcard: Like [type prompt here] & "*" For two wildcards: Like "*" & [type prompt here] & "*" When using a single wildcard it can be placed before or after the prompt. You can use asterisks or question marks, or a combination of both. Example 1. Using a single wildcard In this example a single wildcard has been used, an asterisk.

The parameter… Like [Which Last Name] & "*"

…creates a prompt in which the user can enter the first letter or string of letters of the names they want to see.

The user has entered the text "gr", causing the query to select records with entries in the LastName field of any length starting with the letters "gr". Here's the result...

Example 2. Using two wildcards In this example a two wildcards have been used, both asterisks.

The parameter… Like "*" & [Which Last Name] & "*" …creates a prompt in which the user can enter a letter or string of letter that should occur anywhere in the names they want to see.

The user has entered the text "en", causing the query to select records with entries in the LastName field of any length containing with the letters "en" together. Here's the result...

Get Creative! You can enter a parameter almost anywhere you would normally enter a specific piece of data in your query criteria. Sometimes the syntax (how you write it out) can be a bit tricky, but persevere until you get the result you need. Here are a few examples... Finding records for a specific year (or month) from a collection of dates Supposing you have a field called InvoiceDate containing a range of dates covering several years. Use the following criteria... Year([InvoiceDate])=[Choose a year] ...will create a prompt in which the user can type a year number (e.g. 1998) to see all the records for that year. I you would rather see records for specific months use... Month([InvoiceDate])=[Choose a month from 1-12]) Note that the prompt tells the user to enter a number for the month. Access doesn't understand month names. Finding records for a specific month and year from a collection of dates If you want to be more specific and call for a particular month and year, the criteria... Month([InvoiceDate])=[Choose a month from 1-12] And Year([InvoiceDate])=[Choose a year] ...will present the user with two dialog boxes, the first asking for a month and the second asking for a year. Creating a list of records with dates in the last so many days

You may want to view all the invoices generated in a recent period, such as the last 30 days. The criteria... >Date()-[The last how many days?] ...means "today minus how many days". The user enters a number (e.g. 30) to see a list of dates since that many days before today. The Date() part creates the current date so this query is always up-to-date. What about variable calculations? You can even include parameters as part of the definition of a new calculated field. (If you want to learn about calculating in Access check out the tutorial Calculating in Access Queries) For example, you have a list of invoices in which there is a field called TotalGoods and you need to calculate the discount (or tax or whatever!), but this changes from time to time. You need to create a new calculated field to work out the new figures. Instead of... Discount: [TotalGoods]*25/100 ...which would always calculate a discount of 25% (note: unlike Excel, Access doesn't understand the % sign). You could substitute the fixed figure with a parameter... Discount: [TotalGoods]*[What discount rate - percent]/100 ...which would prompt the user to enter a figure representing the required discount. Asking the Questions in the Right Order When you create a query using more than one parameter, the user sees the prompts in the order that the fields are arranged in the design view of the query, reading from left to right. You normally arrange the fields in the way in which you want to see the results displayed. But what if you want the prompts to appear in a different order? Get to know the Query Parameters Window... Using the Query Parameters windowâ&#x20AC;Ś To control the order in which the prompts appear when running a parameter query containing more than one parameter, you can specify the desired order in the Query Parameters window. Here's how... 1. In the query design view choose Query > Parametersâ&#x20AC;Ś to open the Query Parameters window. 2. In the Parameter column, type the prompt for each parameter exactly as it was typed in the QBE grid. 3. In the Data Type column specify the kind of data (as defined in the table properties). Pick a type from the list. The default type is Text. 4. List the parameters in the order in which you want the dialog boxes to appear when the user runs the query. 24

Click OK to accept your entries and close the window. Here is an example of a query containing two parameters... If you didn't specify otherwise, the prompts would appear in the order that the parameters are arranged in the QBE grid reading from left to right. The user would be asked for a Name first and then a Department. If, however, you make use of the Query Parameters window you can choose the order of the prompts. In this example the parameters have been arranged in a different order so that the user is asked for a Department first and then a Name.

There in no need to make entries in the Query Parameters window if you are happy with the way the query runs, unless you are creating a Crosstab Query containing parameters, in which case you must enter the details of the parameters to make the query run correctly. Dos and Don'ts for Parameter Queries Prompts mustn't match field names When you are designing a parameter query, make sure that the prompt is not exactly the same as one of the field names. You might be tempted to enter the parameter [LastName] to prompt the user to enter a name into the dialog box for the LastName field. This won't do! Because Access uses field names in square brackets for calculations in queries, your entry will not be recognised as a parameter and will not appear as a prompt. If you really want to use the name of the field as a prompt, a simple solution is to turn it into a question by adding a question markâ&#x20AC;Ś [LastName?]. Don't use illegal characters You can type just about anything for the prompt, but you mustn't use the period (.) exclamation mark (!) square brackets ([])or the ampersand (&), everything else is OK. 25

Calculating Dates in Access Queries Access has a number of powerful tools to enable specific dates and date ranges to be specified in criteria. Many tasks can be achieved with simple calculations, and there are a number of date functions to help in performing more complex jobs. Make sure that any fields you have that contain dates know that their data type is Date/Time. This is vital when it comes to sorting, filtering and calculating dates. If you enter a date into a Text or Memo field it will still look like a date to you, but Access will treat it as a string of text. You won't be able to sort into correct date order, nor will you be able to calculate. If you have any problems with dates, check out the design view of the table in which the dates are stored.

Simple Date Calculations When you enter a date into an Access table, Access recognises it as a date, and checks it against the calendar to make sure it is a possible date. You'll get an error message if you try to enter an impossible date such 31st September or 29th February in a non leap year. Then it stores the date as a number known as the date serial. PCs use the 1900 System to store dates. 1st January 1900 was day 1, 2nd January 1900 was day 2 etc. You don't need to know the serial number of your dates, but you can make use of it in mathematical calculations. Here are a few examples…

To add or subtract days from a date... Create a new field with an expression that adds (+) or subtracts (-) the required number from the field containing the original date. For example… Due Date: [Invoice Date]+60

Due Date: creates a new field called Due Date [Invoice Date]+60 takes the date it finds in the Invoice Date field and adds 60 to its serial number. The result is automatically displayed as a date.*[see note below] To calculate the number of days between two dates Create a new field with an expression that subtracts the field containing the earlier date from the field containing the later date. For example… Stay: [Departure Date]-[Arrival Date] 26

Stay: creates a new field called Stay [Departure Date]-[Arrival Date] subtracts the date found in the field Arrival Date from the date found in the field Departure Date. The result is automatically displayed as a number.*[see note below]

Note: If the result fails to display as a date, or displays a date in the wrong format, switch to design view and click in the new field column. On the menu choose View > Properties (or right-click the field and choose Properties from the shortcut menu). Click in the Format section on the General tab of the Field Properties dialog box. Click the down-arrow and choose an appropriate format from the list. Close the dialog box and run the query again to see your dates displayed correctly. Using Date Functions There are four basic date functions which extract part of a date so that it may be displayed on its own, in a new field (further refined with criteria id required), or with additional information as part of the criteria of the field in which the date itself occurs. These functions are…    

Year([Fieldname]) returns the year from a date e.g. 20/8/99 would return 1999 Month([Fieldname]) returns the month from a date e.g. 20/8/99 would return 8 Day([Fieldname]) returns the day of the month from a date e.g. 20/8/99 would return 20 Weekday([Fieldname]) returns the day of the week form a date e.g. 20/8/99 would return 6 representing Friday. The weekdays numbering from 1 to 7 starting with Sunday.

Use these functions in a new field if you want to display the extracted data in addition to the original date. Remember to type the name of the new field first followed by a

colon (:). When you do this you can further refine the query by entering criteria in the new field's criteria row… Year( ) This function is used to extract the year from particular date. In this example, the function is used simply to display the year in a new field… Year : Year([Date]) creates a new field called Year containing data calculated from the field [Date].

If no criteria are defined then all the records are displayed. The usual criteria for defining numbers can be used to display specific years or ranges. For example… 1996 displays records for dates in the year 1996 only. 1996 Or 1997 displays records for dates in 1996 or 1997. >1997 displays records for dates from 1998 onwards. Between 1996 and 1999 displays records for dates in the years 1996, 1997, 1998 and 1999. If you don't need to see the extracted data separately, you can enter the function as part of the criteria of the original date field... The Year( ) function does not have to be used in a separate field. It can form part of the criteria definition. For example… Year([Date])=1997 displays records for dates in 1997 Year([Date])>1995 displays records for dates from 1995 onwards. Remember that you still have to include the name of the field (in this case the field is called [Date]) within the function, even though the criteria are typed in that field's column. 28

The same applies to the Month( ), Day( ) and Weekday( ) functions. For example…

Month([Date])=9 displays records for dates in September Month([Date]) Between 4 and 8 displays records for dates in April, May, June, July and August Day([Date])=15 displays dates which are the 15th day of the month. Weekday([Date])=4 displays dates which are a Wednesday.

Advanced Date Functions Access contains a number of more sophisticated date functions for when you can't get the result you need using a simple calculation or one of the basic date functions. Here's an example... DateAdd(interval, number, [Fieldname]) Use this function to add (or subtract) a specific amount of time to a date. The interval part of the function refers to the type of time unit you want to add and requires you to enter a code…     

yyyy for year q for quarter (i.e. 3 months) m for month d for day ww for week

The number part of the function refers to how many of those units you want to add. You can use a minus number if you want to subtract from the date. Finally you need to supply the name of the field containing the original date. Adding or subtracting days or weeks can be performed with simple mathematical calculations as demonstrated above. The DateAdd function makes it easy to add years, months or quarters to a date. For example…

Date Due: DateAdd("m",2,[Date]) This expression creates a new field called Date Due into which it enters dates from the Date field to which it adds 2 calendar months.

This query could be further refined to display records whose calculated dates fall in a specific range by entering criteria in the new field's criteria row. For example, if these were due dates for invoices where the payment terms were one calendar month, entering <#15/8/99# in the criteria would display only those invoices for which payment was due before 15th August 1999.

Access Query and Filter Criteria When constructing a query or a filter, you need to tell Access what to look for in each field. You do this by defining criteria - typing something (an "expression") into the Criteria cell of the query or filter grid. If you do not define any criteria for a particular field, Access assumes that you are applying no constraints and will display everything it has. This means that you only have to define criteria for those fields you are interested in. Matching Text When you enter text into the criteria cell your text should be enclosed in quotes ("") to distinguish it from other expressions and operators that you may need to add. "Text" To match a word or phrase simply type the text you want to match. The query will find all the records that match the text exactly. Access will add the quote marks at each end. It is only necessary to enter the quotes yourself if you type text that might confuse the query. For example you may want to type a phrase that contains the words "and" or "or". Access would normally interpret these words as instructions. You can manually insert the quote marks at each end of the phrase to make sure the criterion means what you intend it to. This example will display all the records that contain the entry London in the Town field. "Text" Or "Text" To match one of two or more words or phrases, type the text you want to match separated by the word "or". The query will find all the records that match any of the words or phrases. Enter quote marks yourself if you think the text might confuse the query. This example will display all the records that contain either London or Paris in the Town field.

"Text" "Text" To match one of several words or phrases, you can type each word or phrase in a new row moving down the column. This gives the same result as using "or" but has the advantage that your criteria might be easier to read. This example will display all the records that contain the entry London, Paris or Amsterdam in the Town field. Note: If this method is combined with criteria for other fields those criteria must be repeated for each row. In ("Text", "Text", "Text"â&#x20AC;Ś) To match a word or phrase from a list, type the list items separated by commas, and enclose the list in round brackets (parentheses). Access will add the expression "In" and place quote marks where needed - you can do this manually if you wish. This example will display all the records that contain UK or USA or France in the Country field. Not "Text" To exclude a word or phrase, use the expression "Not" followed by the word of phrase you want to exclude (enclosed in quotes). This example will display records that contain anything other than London in the Town field.

Not In ("Text", "Text", "Text"â&#x20AC;Ś) To exclude a list of words or phrases from the search use the same method as for matching from a list but add the expression "Not" at the beginning. This example will display all records that contain anything other than UK or USA or France in the Country field.

Using Wildcards A wildcard is a special character that can stand for either a single character or a string of text. Wildcards are useful when you want the query to look for a range of different possible values, and also when you are not certain exactly what you are looking for but can give the query some clues to work with. The two wildcards we commonly use are the asterisk or star (*) and the question mark (?).The asterisk (*) represents any string of text from nothing up to an entire paragraph or more. The question mark (?) represents a single character only (although you could use, for example, two question marks to represent two unknown characters).For example:   

Yor* would find York, Yorkshire and Yorktown but not New York. Mar? would find Mark but not Mario, Martin or Omar. F*d would find Fred and Ferdinand but not Frederick. Like "Text*" To match text starting with a particular letter or string type the letter or string of text followed by an asterisk. Access will add the expression "Like" and place quotes around your typing. This example will display all records that have an entry starting with S in the Company field.

Like "*Text" To match text ending with a particular letter or string type an asterisk followed by a letter or string of text. This example will display all records that have an entry ending with Plc in the Company field.

Like "[Letter-Letter]*" To match text starting with letters within a certain range you must type the entire expression as shown (this one is too complicated for Access to work out what you want. This example will display all the records with entries starting with the letters A - D in the Company field. You can often get the same results by using mathematical operators such as greater than (>) and less than (<). These are normally used for specifying numbers and dates but can also be used for text. For example:  

<"N" would find all entries beginning with a letter lower than the letter N in the alphabet. In other words, all entries starting with the letters A - M. >"F" And <"H" would find all entries beginning with the letters F and G. 32

Working with Numbers When working with numbers we normally use the mathematical operators to define the range of numbers from which we want to select. For example, where X represents a number:    

<X finds values less than X. >X finds vales greater than X >=X finds values greater than or equal to X <>X finds vales not equal to X

It is important that your field type is correctly defined as a Number field for numerical queries to work properly. Here are some examples… X To match a number simply type the number that you want the query to find. This example will display the record(s) with the entry 385 in the CustomerNumber field.

<X To find values less than a certain number type a less than sign (<) followed by the number. This example will display all records with an entry less than 1000 in the CustomerNumber field. Between X And Y To find values in a range of numbers type the expression shown where X and Y represent the numbers at opposite ends of the range. This example will display all records with entries falling within the range 500-700 in the CustomerNumber field.

Working with Dates Dates behave the same way as numbers, so you can use some of the same techniques when constructing your date query or filter. Remember, for dates to be treated properly by Access it is important that your field type has been correctly defined as a Date/Time field. It doesn't matter how you enter the date, as long as you use a recognised format. The date will be displayed in the resulting dynaset in whatever format you chose when you created the table. When you enter a date in the criteria cell you can use any standard date format, but each date must be enclosed by hash marks (#). For example:   

<#1/1/98# finds dates earlier than 1 January 1998 =#27-Sep-50# finds dates equal to 27 September 1950 Between #5/7/98# And #10/7/98# finds dates no earlier than 5 July 1998 and no later than 10 July 1998

Here are some more examples… =#Date# To match a particular date type the date enclosed by hash marks (#). This example will display all the records with entries for 27 September 1998 in the Invoice Date field.

=Date() To match today's date type the expression shown. Date() means "today". This example will display all the records with entries for the current date in the Invoice Date field.

Year([Fieldname])=Year(Now()) To match the current year type the expression shown, entering the name of the current field in square brackets where indicated. This example will display all the records with entries for the current year in the Invoice Date field. Year([Fieldname])=Year To match a particular year type the expression shown, entering the name of the current field in square brackets where indicated and the required year in place of Year. This example will display all the records with a date in 1998 in the Invoice Date field.

<Date()-30 To match a particular calculated date range you will need to use a combination of expressions. This expression employs a calculation that subtracts 30 from the current date and also includes the less than operator. This example will display all the records with a date more than 30 days old in the Invoice Date field. Excluding Things Sometimes you want to specifically exclude criteria from your search. This is done with the expression Not. This expression can be used on its own or in combination with other expressions. For example: ď&#x201A;ˇ ď&#x201A;ˇ

Not "text" finds all records except those matching the specified text. Not Like "X*" finds all records except those starting with the specified letter (or string of text).

Here are some more examples: Not "Text" To exclude specific records from the search use the expression Not followed by the text which matches those records you want left out. The text needs to be between quotes as shown here - Access will normally do that for you. This example will find all records for contacts in towns other than London.

Not Like "Text*" You can use wildcards with the Not expression, which then becomes Not Like followed by your wildcard criteria. Here is just one example. This example will find all records for contacts in towns starting will letters other than L.

And Not "Text" The Not expression can be used in combination with other expressions, when it becomes And Not followed by the text you want to exclude from your search. This example will find all records for contacts in towns starting with the letter L but will exclude those in London. Finding Empty Fields A query can be used to find records where specific fields are empty. To do this you use the expression Is Null. Conversely, to find records for which specific fields are

not empty you use the expression Is Not Null. The expression Null simply means "nothing". Here are some examples: Is Null To find empty fields use the Is Null expression. This looks for fields that contain no data. This example will find all records for contacts whose fax number has not been recorded.

Is Not Null To find fields that are not empty use the Is Not Null expression. This looks for fields that contain data. If there is something in the field the record will be shown. Note that Is Not Null will find fields containing zero length entries. (If you want to leave them out try excluding them with the And Not expression.) This example finds all records for contacts whose fax number has been recorded.

"" To find zero length entries use "" expression. This looks for zero length entries in the specified field. This example would find, depending on why you had made use of the zero length entry feature, all records for contacts who did not have a fax.

Use a form to display a list of reports and queries in an Access database Ever wonder exactly what reports and queries are being created for a database? As an administrator, you may need a quick way of determining what reports and queries are being run against a database. Fortunately, Access stores the names of all the top-level database objects in a system table called MSysObjects. You can run queries against MSysObjects just as you would any other table in the database. Follow these steps: 1. Open the database and create a form with two unbound list boxes, as shown in Figure A. Figure A

1. In Design view, right-click the first list box and select Properties. 2. Under the Data tab, right-click the RowSource property box and select Zoom. 3. Enter the code as shown in Figure B and click Close.

Figure B

1. Right-click the second list box and select Properties. 2. Under the Data tab, right-click the RowSource property box and select Zoom. 3. Enter the code as shown in Figure C. Figure C

When you run the form, Access outputs all current reports and queries for that database to the respective list boxes (Figure D).

Figure D

To access other object types, use their type value. For example, to list all the tables in the database, enter 6. To list forms, enter 32768. To list macros, enter -32766.

Macros How to Make a Macro in Word 2010 A macro is a teensy program you can write in Word 2010 that automates things, such as repetitive keystrokes or tasks. You start making a macro by recording it.

Enlarge

1 In the View tab, choose Macros→Record Macro. The Record Macro dialog box appears.

2 Give the macro a name in the Record Macro dialog box. Click the Keyboard button to assign a keyboard shortcut to the macro. This approach is easier than choosing the Button option, which is more work.

3 Type a keyboard shortcut combination. Most of the good combinations are already used by Word, though many of the Ctrl+Alt+letter combinations aren't. Click the Assign button.

4 Click the Close button. You're now recording a macro in Word. Everything you do is recorded, from typing text to choosing commands and setting options.

Enlarge

5 To stop recording, choose Macros→Stop Recording. The macro is saved.

6 To play back the macro, press the keyboard shortcut you assigned. Word repeats all actions taken while the macro was being recorded, playing them back as though you just issued the commands or typed the text yourself. To review macros you’ve made, choose Macros→View Macros. You can manually run a macro from the Macros dialog box, or you can rename, edit, or delete the macros.

Adding a comment to your macro code: Adding Comments in VBA Any comment in VBA begins with a single apostrophe character ('). When you type in a comment and press the Enter key, the line will go green. For comments spanning multiple lines, you'll need to type the ' key multiple times:

Macro in Excel

Introduction A macro command is a series of instructions that are always executed one after the other in the same order. They're very practical to automate repetitive tasks. The exercise that follows will demonstrate how to create a macro command. The next macro basically changes the background color of the selected cells. It contains only that single command. But after you've finished, you will be able create your own "macros" and insert as many instructions as you need.

Creating of a macro command

Write the following numbers in the appropriate cells. Place the cursor in the A1 cell. You must place the cursor in that cell before you can begin your "macro". You'll see why later on.

From the Tools menu, select the Macro option. Select the Record New macro option.

A new window will open asking you for some information about the new macro. The first box asks you for the name that you give to this macro. You can also put a letter or a number in the shortcut box. You'll be able to execute the macro by pressing the Ctrl and a keys. You can place in any letter or number that you want. The shortcut key is compulsory. The window asks you if you want to store the macro in this file or in another worksheet file. It's possible "to reuse" the macro commands in a personal macro file. The same macro can then be used for several files. But this is only for those that are really serious about using macros. For the exercise: Give a name to your macro. It should represent the actions that will be done such as " Printing_the_budget" . The name of the macro cannot have any spaces. An underline can be used to link words. It's also possible to have a shortcut key to activate a macro command. This avoids you having to go into the Tools menu, followed by the Macro, Macro commands, select the macro of your choice and pressing on the Execute button. Enter the data as shown in the picture above. Press the OK button. All the actions that you go to make will be added to the macro command until you stop the recording.

As soon as you press the OK button, the window disappears and a small toolbar appears in its place. This small toolbar has only two buttons. The first one is to stop the macro from recording. The second is to activate or deactivate the relative reference option. This can be important according to the type of macro that you want to carry out. You do not activate this option if you want the macro to do whatever you want it to do at always the same location. You activate this option if you want the macro to start where the active cursor is located. There will be more details of this option a little later on this page. Press the second button to be sure that the relative option is activated. (It's very important for this demonstration). Make a block out of the A1 to C1 cells.

Press the Fill button to change the cell's background color to the color of your choice.

Press the first button of the macro toolbar to stop the macro from recording. OR From the Tools menu, select the Macro and Stop recording options. The new macro command is now complete. It could have had a lot more instructions than this example. But this is only to demonstrate what a macro can do. It's time to see if you may repeat it. Place the cursor in the A3 cell. Press the Ctrl and A keys to activate the shortcut to the macro. Here is the result of the macro. The cells of the third row have now the same background color as the one you chose for the cells of the first row.

There is an explanation if it didn't work. You forgot to activate the relative position option when asked. Excel will repeat the macro at the same location instead of beginning it where the cursor is located. It's as for that reason that it was asked to you to move the cursor in the A3 cell and to activate the relative button. Because the relative reference option was activated, you may execute the macro to another place rather than where it was created. You just need to put the cursor where you need to activate the macro. If the relative option is not activated, the macro will always repeat itself at the same location. It can be practical for your needs. It depends on the box. You decide when you should activate the relative reference option or not. It's practical when you know that you'll want to apply the macro to another place in your file. For the last exercise, it was necessary to be able to apply it to the A3 to C3 cells. A 1004 error message can also appear. Generally, it's because you forgot to stop the macro command from recording. The macro is then caught in an endless loop. The macro is recalled before it's even finished! It will be necessary to change the macro.

Change a macro command From the Tools menu, select the Macro option Select the macro of your choice and press the Edit button.

The Visual BASIC editor will appear with the code of the macro command that you want to change.

The macro indicates that three cells were selected (A1 to C1). It then indicates the color as well as the pattern of the background of the selected cells.

Print a macro command It's possible to print the code while changeing the macro command. From the File menu, select the Print option. To return to Excel... From the File menu, select the Close and return to Microsoft Excel option.

Attach a macro command to a button It's sometimes very interesting to be able to execute macro commands by just pressing a button; even more if you leave your file to another person. They probably don't know all the macro commands that you created to work faster. The next exercise consists in attaching a macro command to a button. IMPORTANT: You must already have created a macro that you will need before attaching it to a button. From the Edit menu, select the Toolbar option. From the list of the available toolbars, select the Forms toolbar.

Move the toolbar if it's necessary. Place the cursor on the titles bar of the toolbar (the blue bar on top). Press the left mouse button and move the toolbar towards the place of your choice. Once the toolbar is at its new location, release the left mouse button. This toolbar is used for creating forms in Excel files. It facilitates the use of the model for the users.

This toolbar offers you several objects, or controls, that you can place on forms. For the moment, you only have to worry about the button to attach a macro command. Press the button. Press the left mouse button and determine the place and size of the button. When satisfied with the size, release the left mouse button. Excel will automatically ask you for the name of the macro that you want to attach to the newly created button.

Select the macro of your choice. Press the OK button. While the button is still selected, you can change the text on the button to whatever you need it to be. Once finished, click outside of the button. To execute the macro that's now connected to the button. Place the cursor in the A5 cell. Place the cursor over the button. Press the left mouse button.

The button now executes the macro. You can apply any macro that you make to a button. It's that easy! If you wish to change the options of the button. Place the cursor over the button. Press the right mouse button. A context menu will appear next to the button.

If you want to affect the presentation of this macro button, select the Format Control option. The properties window of the button will allow you to change all the options of your choice. All the possible options you will be found under these seven tabs: Font, Alignment, Size, Protection, Properties, Margins and Web.

Under the Font tab you will find all the options for the presentation of the text on the button. You can change the font, font style, size, color and effects.

The Alignment tab allows you to change the placement of the text inside the button including its' orientation.

The Size tab allows you to determine exactly the size of the button on the worksheet.

Like for the protection for cells, it's also possible to protect buttons under the Protection tab. By default, all the buttons are protected when the protection is activated. You should leave it protected unless you want the user to be able change the button properties for some reason.

The Properties tab allows you to decide if the button should change size and placement when you change you change you size and placement of the cells of the worksheet below it. You can also decide to move or not the button if you insert or delete rows and columns. By default, the button will not be printed unless you activate the Print the object option.

The Margins tab allows you to control the margin, or the space between the text on the button and its border. You can use the predetermined margins or change them to your choice.

You can always save this worksheet as a Web page. So Excel offers you some Web properties under the Web tab. For buttons, it only allows you to put some alternate

Copy/Paste Special How to Use Paste Special in Excel 2010 Microsoft Excel 2010 normally copies all the information in the range of cells you select when you paste the data. Use Excel's Paste Special command to specify other options, such as pasting only the cell contents (without the formatting) or only the formatting (without the cell contents). To paste particular parts of a cell selection, click the Paste button arrow on the Ribbon's Home tab. Then, click Paste Special on its drop-down menu to open the Paste Special dialog box.

Paste only some of a copied or cut cell's properties with Paste Special. You can specify which parts of the current cell selection to use by selecting the appropriate Paste Special options: 

All to paste all the stuff in the cell selection (formulas, formatting, you name it). This is what happens when you paste normally.



Formulas to paste all the text, numbers, and formulas in the current cell selection without their formatting.



Values to convert formulas in the current cell selection to their calculated values.



Formats to paste only the formatting from the current cell selection, without the cell entries.



Comments to paste only the notes that you attach to their cells (kinda like electronic self-stick notes).



Validation to paste only the data validation rules into the cell range that you set up with the Data Validation command.



All Using Source Theme to paste all the information plus the cell styles applied to the cells.



All Except Borders to paste all the stuff in the cell selection without copying any borders you use there.



Column Widths to apply the column widths of the cells copied to the Clipboard to the columns where the cells are pasted.



Formulas and Number Formats to include the number formats assigned to the pasted values and formulas.



Values and Number Formats to convert formulas to their calculated values and include the number formats you assigned to all the copied or cut values.



All Merging Conditional Formats to paste conditional formatting into the cell range.

When you paste, you can also perform some simple math calculations based on the value(s) in the copied or cut cell(s) and the value in the target cell(s): 

None: Excel performs no operation between the data entries you cut or copy to the Clipboard and the data entries in the cell range where you paste. This is the default setting.



Add: Excel adds the values you cut or copy to the Clipboard to the values in the cell range where you paste.



Subtract: Excel subtracts the values you cut or copy to the Clipboard from the values in the cell range where you paste.



Multiply: Excel multiplies the values you cut or copy to the Clipboard by the values in the cell range where you paste.



Divide: Excel divides the values you cut or copy to the Clipboard by the values in the cell range where you paste.

Finally, at the bottom of the Paste Special dialog box, you have a few other options: 

Skip Blanks: Select this check box when you want Excel to paste only from the cells that aren't empty.



Transpose: Select this check box when you want Excel to change the orientation of the pasted entries. For example, if the original cells' entries run down the rows of a single column of theworksheet, the transposed pasted entries will run across the columns of a single row.



Paste Link: Click this button when you want to establish a link between the copies you're pasting and the original entries. That way, changes to the original cells automatically update in the pasted copies.

Applied ICT- functions http://www.techonthenet.com/excel/formulas/ Function

Syntax

Example/use

Average

=average(cell1, cell2)

=average(a1:b10) Works out the average for a range of cells.

Concatenat e

=concatenate(text1,text 2)

=concatenate(a1,a2) adds 2 or more strings together.

Count

=count(range of cells)

=count(A1:A6) Counts the number of cells that contains numbers.

Counta

=counta(range of cells)

=counta(a1:a6) counts the number of cells that contains values.

Countif

=countif(range, criteria)

=countif(a1:a6,”Paul”) counts the number of cells in a range that contains “Paul”

Day

=day(date)

=day(A1) would return the day part of a date. Eg 15/03/2010 would return 15.

Hlookup

=HLookup( value, table_array, index_number, not_exact_match )

=HLookup(10251, A1:K3, 2, FALSE)

Hour

=hour(time)

=hour(10:23) would return the value of 10.

=If( condition, value_if_true, value_if_false )

=if(A2>10, “excellent”, “rubbish”) would display the word excellent if the contents of cell A2 are greater than 10.

Int

=int(expression)

=int(a1) would return the integer value of cell a1. Eg 10.4 would become 10

Left/right

=left/right(text,number of characters)

=left(a1,5) if A1 contained the word alphabet would return the text alpha.

LEN

=len(text)

=len(a1) returns the length of the string in

Would return the 2nd row in the range of cells that matched 10251.

cell A1 Lookup

=Lookup( value, lookup_range, result_range )

=Lookup(10251, A1:A21, B1:B21) would return "Tofu" returns the text Tofu from the 2nd range after identifying 10251 in the 1st range.

Mid

=Mid( text, =Mid(A1, 5, 4) would return "abet" from the start_position, text alphabet. number_of_characters )

Min/max

=minx/max(Max( number1, number2, ... number_n )

=min/max(A1:A10) returns the lowest/highest values in the range of cells a1 to a10.

Minute

=minute(A1)

=minute(A1) returns the minutes value in the cell A1 that contains a time.

Month

=Month( date_value )

=month(A1) would return the number of the month from a Date cell.

Now

=Now()

Returns the current system time and date.

Round

=round(number,digits)

=round(A1,2) rounds the number in cell A1 to 2 decimal places.

second

=second(time)

=second(A1) returns the seconds value of a time cell.

Subtotal

=Subtotal( method, range1, range2, ... range_n )

Subtotal function returns the subtotal of the numbers in a column in a list or database. method is type of subtotal that you'd like to create. It can be one of the following values: Value Explanation 1

AVERAGE

COUNT

COUNTA

MAX

MIN 56

PRODUCT

STDEV

STDEVP

SUM

VAR

VARP

Based on the Excel spreadsheet above: =Subtotal(1, D2:D5) would return 22.3925 =Subtotal(2, D2:D5) would return 4 =Subtotal(3, D2:D5) would return 4 =Subtotal(4, D2:D5) would return 35.88 =Subtotal(5, D2:D5) would return 7 =Subtotal(6, D2:D5) would return 136191.51 =Subtotal(7, D2:D5)

would return 11.91825316

=Subtotal(8, D2:D5) would return 10.32151 =Subtotal(9, D2:D5) would return 89.57 =Subtotal(10, D2:D5)

sum

=Sum ( cell1:cell2 )

Sumif

=SumIf( range, criteria, sum_range )

would return 142.0447583

=Subtotal(11, would return D2:D5) 106.5335688 =Sum(B2:B6) would add up 231.2 (the contents of cells b2 to b6. =SumIf(A2:A6, D2, C2:C6) would add up the values in C2 to C6 that are equal to the 57

value in cell D2. Text

=Text( value, format )

=Text(A2, "0.0") would return "123.7" returns the number in A2 as a number with decimal place.

Transpose

=Transpose( range )

=TRANSPOSE(A1:A3) moves the cells from A1 to A3 into a new range of cells eg c1 to E1

Value

=Value( text )

Converts a text value into a number.

Vlookup

=VLookup( value, table_array, index_number, not_exact_match )

=VLookup(10251, A1:B21, 2, FALSE) would return "Tofu" â&#x20AC;&#x201C; the value in the 2nd column of A1 to B21 that equals the value in 10251

Weekday

=Weekday( serial_number, return_value )

=Weekday(A1) returns the number of the day in the cell A1 that is a date.

Year

=Year( date_value )

=Year(A1) returns the year of the cell A1 that is a date.