week 8

C1 Developing a data model solution

Sub FileQuote()
'
' FileQuote Macro
'
'
    Sheets("Quotation Record").Select
    Rows("2:2").Select
    Application.CutCopyMode = False
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    Sheets("Data").Select
    Range("A2:N2").Select
    Selection.Copy
    Sheets("Quotation Record").Select
    Range("A2:N2").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Sheets("Quotes").Select
    Range("C3").Select
End Sub

How to clear a excel data sheet.
 
Sub ClearScreen()
'
' ClearScreen Macro
'
'
    Range("C3").Select
    Selection.ClearContents
    Range("C4").Select
    Selection.ClearContents
    Range("C5").Select
    Selection.ClearContents
    Range("C6").Select
    Selection.ClearContents
    Range("C7").Select
    Selection.ClearContents
    Range("D9").Select
    ActiveCell.FormulaR1C1 = "1"
    Range("D11").Select
    ActiveCell.FormulaR1C1 = "1"
    Range("C13").Select
    ActiveCell.FormulaR1C1 = "17"
    Range("D15").Select
    ActiveCell.FormulaR1C1 = "1"
    Range("D17").Select
    ActiveCell.FormulaR1C1 = "1"
    Range("D19").Select
    ActiveCell.FormulaR1C1 = "FALSE"
    Range("C21").Select
    ActiveCell.FormulaR1C1 = "0"
    Range("C3").Select
End Sub

Processing features and requirements:

formulae, e.g. add, subtract, divide, multiply

Week 1  Web view

functions e.g. SUM, AVERAGE, COUNT, COUNTIF, LOOKUP, INDEX, GOAL SEEK

Week 1  Web view

Week 7 Look-up Functions  Web view

logical functions e.g. IF, NOT, AND, OR, WHATIF

Week 5 Nested If Functions  Web view

nested IF functions

Week 5 Nested If Functions  Web view

data manipulation, e.g. sorting, grouping, filtering, pivoting data

Week 12 Sorts Filters  Web view

Week 13 Pivot Tables  Web view

importing and exporting data

Importing 

1. Go to File > Open.

2. Select Text Files from the Open dialog box.

3. Locate and double-click the text file that you want to open.

Exporting

You can convert an Excel worksheet to a text file by using the Save As command.

1. Go to File > Save As.

2. The Save As dialog box appears.

3. In the Save as type box, choose the text file format for the worksheet.

   • For example, click Text (Tab delimited) or CSV (Comma delimited).

autofill

replication

relative and absolute cell referencing

Week 2 Calculations  Web view

using macros and buttons to initiate procedures.

Week 10 Macros  (Web view)

Data entry and validation requirements and methods:

use of data entry forms

Week 9 Form Controls  Web view

restricting data input to acceptable values

Week 3 Validation  Web view

protecting cells by hiding, locking and password protecting

ease of use techniques, e.g. list boxes and drop-down menus

automated data transfer between sheets or applications

adding user prompts and messages.

Week 3 Validation  Web view

Layout and presentation considerations:

font size and style

merging cells

colours, borders, shading

conditional formatting

headers and footers

graphics

Output requirements:

worksheet layout

graphics

colours, borders and shading

charts and graphs.

Week 4 Charts and Graphs  Web view

Last Updated
2019-03-18 11:49:23