Taking Formulas to the Next Level

Taking Formulas to the Next Level

About this course

In this course, you'll learn about the following:
 
  • Weakest link:
    • Learn how leveraging the COUNTIF in your nested IF formulas at the parent level gives you visibility at the lowest level or weakest CHILDREN tasks.
  • Ancestors:
    • Use the ANCESTOR hierarchy function to COUNT the number of hierarchical levels in your sheet and then apply conditional formatting to your sheet based on your levels.
  • Parent Logic and Levels:
    • Combine the COUNT(CHILDREN()) and the ANCESTORS function to apply hierarchical levels only to parent rows. Useful when you wish apply Conditional Formatting only to rows that are parents.
  • Comprehensive Health:
    • Apply all that we’ve learned to create a comprehensive formula to automate unique business logic to CHILDREN and PARENT rows.
  • Breadcrumbs:
    • Use the PARENT and ANCESTOR functions to display the Primary Column hierarchical details (Breadcrumbs). This is useful when reporting CHILDREN tasks where you need to reference it’s PARENT level detail. Add the JOIN function to apply more than one level of detail.
  • This Week:
    • Use the combination of WEEKNUMBER and TODAY date functions to mark rows where a specified Date column is scheduled for this week. This formula is useful for running dynamic reports of tasks scheduled for this week. Use a CHECKBOX column to indicate the rows that are returned to the Report.

Total video time: 39 minutes

Curriculum

  • Introduction
  • Activity Asset Downloads
  • Weakest Link
  • Ancestors
  • Parent Logic + Levels
  • Comprehensive Health
  • Breadcrumbs
  • This Week
  • Wrap-up
  • Knowledge Check
  • Course Evaluation

About this course

In this course, you'll learn about the following:
 
  • Weakest link:
    • Learn how leveraging the COUNTIF in your nested IF formulas at the parent level gives you visibility at the lowest level or weakest CHILDREN tasks.
  • Ancestors:
    • Use the ANCESTOR hierarchy function to COUNT the number of hierarchical levels in your sheet and then apply conditional formatting to your sheet based on your levels.
  • Parent Logic and Levels:
    • Combine the COUNT(CHILDREN()) and the ANCESTORS function to apply hierarchical levels only to parent rows. Useful when you wish apply Conditional Formatting only to rows that are parents.
  • Comprehensive Health:
    • Apply all that we’ve learned to create a comprehensive formula to automate unique business logic to CHILDREN and PARENT rows.
  • Breadcrumbs:
    • Use the PARENT and ANCESTOR functions to display the Primary Column hierarchical details (Breadcrumbs). This is useful when reporting CHILDREN tasks where you need to reference it’s PARENT level detail. Add the JOIN function to apply more than one level of detail.
  • This Week:
    • Use the combination of WEEKNUMBER and TODAY date functions to mark rows where a specified Date column is scheduled for this week. This formula is useful for running dynamic reports of tasks scheduled for this week. Use a CHECKBOX column to indicate the rows that are returned to the Report.

Total video time: 39 minutes

Curriculum

  • Introduction
  • Activity Asset Downloads
  • Weakest Link
  • Ancestors
  • Parent Logic + Levels
  • Comprehensive Health
  • Breadcrumbs
  • This Week
  • Wrap-up
  • Knowledge Check
  • Course Evaluation