1 of 24

Problem Set 10 : My CCA�SST Data Validation

+

2 of 24

Problem Set 10

Data Validation�10.2 Form Creation

+

3 of 24

Lesson Objectives |PS10.2 SST

At the end of this lesson, you should be able to :

Design a CCA selection form (Pg 148)
Set data entry instructions
Set data validation checks
Create a nested IF statement
Protect cell contents so users cannot edit them!

+

4 of 24

Problem Set 10 : My CCA�Pg 148 – Pg 154

Worksheet 10.2 : �Part A: SST – Designing a CCA Selection Form

Filename: “CCA Selection Form.xls”

Part B: SST – Gym Membership Form

Filename: “gym membership.xls”

+

5 of 24

Problem Set 10 : My CCA�Data Validation Sequence

1. �Select Cell or Range of cells for Data Validation

2. �Select Data

3. �Select Data Validation

4. �Select Data Validation on Dropdown

+

6 of 24

PS10.2 : CCA Selection Form�Pg 148 – Pg 154 | CCA Selection Form.xls

In E3, enter a function to display the current date.
Look at the section “Personal Details” �Set your own instructions using ➔ Data/Data Validation

No	Requirement	Question	Data Validation Type
a	Give instructions to enter the name in full.
b	Give instruction to enter data with the required length.	What is the length of the NRIC?	Length
c	Give instruction to select the gender as �M or F.	What if someone chooses anything else?	Type
d	Give instruction to enter the date in the dd/mm//yy format	Data validation:�type	Type

+

7 of 24

PS10.2 : CCA Selection Form�Pg 148 – Pg 154 | CCA Selection Form.xls

a. Write an IF statement to check that …

No	Requirement	Formula
1.	Male students are not allowed to join Girl Guides.	=IF (cellAddress = “F”, “Girl Guides”, “Scouts”)
2.	Female students are not allowed to join Scouts.	=IF (cellAddress = “F”, “Girl Guides”, “Scouts”)

IDEA!

If(cellAddress = “F”) ➔“Girl Guides”,

Else ➔ “Scouts”

…

= IF (B9 = “F”,”Girl Guides”,”Scouts”)

+

8 of 24

PS10.2 : CCA Selection Form�Pg 148 – Pg 154 | CCA Selection Form.xls

a. Write a formula to calculate age

Concept�We use YEAR (Today) – YEAR (Birthday)

…

YEAR (date)

➔ returns the year of the date

+

9 of 24

Problem Set 10 : My CCA�Functions & Data Validation {Length}

Function to calculate length of string.
Instructions for user to enter remarks not more than 50 characters.

+

10 of 24

Problem Set 10 : My CCA�Protecting Cells

Protect the content cells in E9, E10 and C23 �so that the user cannot edit them.

+

11 of 24

Problem Set 10.2 : Part B |Pg 150�Protecting Cells

Protect the content cells in E9, E10 and C23 �so that the users cannot edit them.�➔ the video has also been archived on �theCPAchannel SST playlist.

Check out this video tutorial on cell protection.�https://www.youtube.com/watch?v=dtoyhXBikWo

+

12 of 24

Submission

Filename:

CCA_Selection_Class_IndexNo_ Name.xls

CCA_Selection_08_ YourName.xls

+

13 of 24

Conclusion

At the end of this lesson, you should be able to :

Design a CCA selection form (Pg 148)
Set data entry instructions
Set data validation checks
Create a nested IF statement
Protect cell contents so users cannot edit them!

+

14 of 24

Problem Set 10

Session Objectives

+

15 of 24

Session 1 {Lesson Objectives}

At the end of this lesson, you should be able to

Differentiate between the 3 data validation types � Length, Range, Type (LRT)
Experience examples on error messages
Understand the string functions LEN, MID, LEFT
Understand the date functions TODAY()

+

16 of 24

Session 2 {Lesson Objectives}

At the end of this lesson, you should be able to

Differentiate between the 3 data validation types � Length, Range, Type (LRT)
Experience examples of error messages WS 10.1 (a)
Key in accurate data types
Use string functions LEN, MID, LEFT WS 10.1 (b)
Use the date functions TODAY() WS 10.1 (a)�.��WS 10.1 is made up of 2 worksheets� a. Data Validation

b. Data Extraction

+

17 of 24

Session 3 {Lesson Objectives}

At the end of this lesson, you should be able to

Create a form with data validation WS 10.2
Set your error messages
Set protection cells

+

18 of 24

Problem Set 10

Concept Recap

+

19 of 24

What is a Data Validation?

Data validation is an automatic process

using a set of rules (or conditions)
to check if a data type (text, date, numbers, decimals) can be used for a cell or a range of cells.

+

20 of 24

Why is the data invalid?�How do we set the data validation?

Why Data Validation?

To help users to enter the right data type into the required cells.

Which software allow Data Validation be used?

Data validation is used in MS-Excel.

It can also be used in Google Spreadsheets.

+

21 of 24

Type of Data Validation | LRT

No	Data Validation Types	Explaination	Example
1	Length	Checks the length of the data	Is the password long enough?
2	Range	Checks the upper limit & lower limit of data	More than 5 characters less than 8 characters
3	Type	Check the type of data	Numbers, decimals, text

+

22 of 24

Functions | String & Date functions

LEN // to find the length of a string
MID // to extract the part of a string
LEFT // extract characters from left
TODAY( ) // returns today’s date

…

No	Function	Example	Result
1	LEN(CellAddress)	= LEN(Juying)	6
2	MID(Cell_Address, 3,4)	= MID (ABCDEFGH,3,4)	CDEF
3	LEFT(Cell_Address,2)	= LEFT (Ali Baba,2)	Al
4	=TODAY ()	=TODAY ()	21-Jan-2015

+

23 of 24

Problem Set 10.1 : My CCA�Pg 143

Why is it necessary to do data validation?

To help users to key in the correct data type.
To make sure the data entered meets requirements.

Write down the functions that can be used to:

Extract ‘4728’ from T0134728F’

= MID(T0134728F , 5, 4)

Extract ‘M’ from ‘M201348’

= LEFT(M201348 , 1)

Complete the true and false in Q3

+

24 of 24

A New Format

Font: Rockwell

Rockwell is cool as cool.

+