e04ch08_powerpoints (1).pdf

Your Office: Microsoft Office 365 Excel 2019 Comprehensive Fourth Edition Chapter 8 Building an Application with Multiple Worksheets...

Your Office: Microsoft Office 365 Excel 2019 Comprehensive Fourth Edition Chapter 8 Building an Application with Multiple Worksheets and Workbooks Copyright © 2020, 2017, 2014 Pearson All Rights Reserved Learning Objectives (1 of 2) 8.1 View formula precedents and dependents 8.2 Evaluate formulas 8.3 Correct circular references 8.4 Use the Watch Window Copyright © 2020, 2017, 2014 Pearson All Rights Reserved Learning Objectives (2 of 2) 8.5 Control data entry with data validation 8.6 Create and use macros 8.7 Change how to navigate a workbook 8.8 Protect workbooks and worksheets Copyright © 2020, 2017, 2014 Pearson All Rights Reserved View Formula Precedents and Dependents Figure 1 Trace Precedents and Trace Dependents arrows Copyright © 2020, 2017, 2014 Pearson All Rights Reserved Evaluate Formulas Figure 2 Evaluate Formula dialog box Copyright © 2020, 2017, 2014 Pearson All Rights Reserved Correct Circular References Figure 3 Circular reference as indicated by Trace Precedents Copyright © 2020, 2017, 2014 Pearson All Rights Reserved Use the Watch Window Figure 4 Watch Window Copyright © 2020, 2017, 2014 Pearson All Rights Reserved Control Data Entry with Data Validation (1 of 10) Validation Type Use Any value Allows use of input messages Whole number Specific range of integers Decimal Limits the number of decimals used List Predefined list of values Copyright © 2020, 2017, 2014 Pearson All Rights Reserved Control Data Entry with Data Validation (2 of 10) Validation Type Use Date Data entered into the cell must represent a valid date Time Cell data cell must be a valid time Text length Places a limit on the number of text characters in a cell Custom A formula determines if data meets a validity test Copyright © 2020, 2017, 2014 Pearson All Rights Reserved Control Data Entry with Data Validation (3 of 10) Simple data validation—restricted to a single validation criterion per cell Validation criteria can specify: − Input message—appears when a user makes a validated cell active − Error alert—informs that entered data violates validation constraints ▪ Stop ▪ Warning ▪ Information Copyright © 2020, 2017, 2014 Pearson All Rights Reserved Control Data Entry with Data Validation (4 of 10) Figure 7 List data validation rule Copyright © 2020, 2017, 2014 Pearson All Rights Reserved Control Data Entry with Data Validation (5 of 10) Figure 9 Data validation style Copyright © 2020, 2017, 2014 Pearson All Rights Reserved Control Data Entry with Data Validation (6 of 10) Figure 10 Date validation with formula Copyright © 2020, 2017, 2014 Pearson All Rights Reserved Control Data Entry with Data Validation (7 of 10) Figure 11 Time data validation Copyright © 2020, 2017, 2014 Pearson All Rights Reserved Control Data Entry with Data Validation (8 of 10) Figure 12 Whole number data validation Copyright © 2020, 2017, 2014 Pearson All Rights Reserved Control Data Entry with Data Validation (9 of 10) Figure 14 Text data validation Copyright © 2020, 2017, 2014 Pearson All Rights Reserved Control Data Entry with Data Validation (10 of 10) =AND(LEFT(E12,1)="1",RIGHT(E1 2,3)>="001",RIGHT(E12,3)