Google Sheet: https://docs.google.com/a/mylaurence.com/spreadsheets/d/1Kcc5TFoItt3DVmtGkAncyPdwyCxP5SdhNNhceDZfghs/edit?usp=sharing
Automatically create rosters from another sheet.
Use FILTER to filter out specific rows of data then use SORT to create readable rosters.
=IFERROR(FILTER(Wufoo!F2:H2,Wufoo!I2="Language Arts"))
=SORT(FILTER!A2:C60,2,TRUE)
Syntax:
IFERROR(value1, value2)
IFERROR(value1)
IFERROR will return value1 if value itself is not an error. It will return value2 if it is an error. Excluding value2 will return a blank cell if there is an error.
Syntax:
FILTER will return the value from range when a condition is met.
Syntax:
SORT(range, sort_column, ascending? [repeatable])
SORT will arrange the column alphabetically.
Automatically count the number of occurrences for a certain value within a range.
="Math = "&(COUNTIF(Wufoo!I2:I40, "Math") + COUNTIF(Wufoo!O2:O40, "Math"))
Syntax:
COUNTIF(range, criterion)
Multiple instances of COUNTIF can be used to add the occurrences of the same values throughout a set of ranges.
Highlight Duplicate Values
=COUNTIF(A:A, A1) > 1
Use conditional formatting with a custom formula.
Creating Readable Dates That Can Be Used In Formulas
1. Convert the number values from three cells into a calendar date:
=DATE(O1,P1,Q1)+0
=DATE(O1,P1,Q1)+1
...
2. Convert the new date value into a text:
=TEXT(N3,"MM/DD")
=TEXT(N4,"MM/DD")
...
3. The generated MM/DD values can now be assigned a numerical weekday:
=WEEKDAY(O3,1)
=WEEKDAY(O4,1)
...
4. Finally, a nested IF statement can be used to generate a new text value from the weekday. Unlike the WEEKDAY value, the text value is usable on other formulas:
=IF(P3 = 2, "Monday", IF(P3 = 3, "Tuesday", IF(P3 = 4, "Wednesday", IF(P3 = 5, "Thursday", "ERROR"))))
=IF(P4 = 2, "Monday", IF(P4 = 3, "Tuesday", IF(P4 = 4, "Wednesday", IF(P4 = 5, "Thursday", "ERROR"))))
...
5. With our newly generated date formulas, we can automatically update the days and dates across the worksheet:
=(FILTER!R3) & " " & (FILTER!O3) & " (Student 1)"
