ONLY
ONLY(set)
The single row of a set that should hold exactly one, such as the one predecessor of a task you expect to have one. Unlike FIRST, it doesn't choose: an empty set or a set of two or more is an error on that row, so a schedule that breaks your assumption shows up instead of giving a quiet answer.
Arguments
| Argument | What to give it |
|---|---|
| set | A set of rows that should hold one: Predecessors, FILTER(Assignments, …). |
Examples
Formula=ONLY(Predecessors)[Finish date]
The finish of the task's predecessor. Tasks with no predecessor, or more than one, show an error.
Formula=ONLY(FILTER(Assignments, Assignments[Resource] = "Crane"))[Budgeted units]
The planned crane hours on the task, where a task carries at most one crane assignment. A task with none shows an error too: wrap it in IFERROR to give those 0.
Formula=IFERROR(ONLY(Predecessors)[Task code], "not one")
The code of the task's one predecessor, or "not one" when it has none or several.
Reach for something else when
| You want | Use |
|---|---|
| Any one row, with a blank when there is none | FIRST |
| The row with the smallest or largest value | ARGMIN or ARGMAX |
| A value from every row, combined | an aggregate such as MAX or SUM |
Common questions
Why do I need ONLY when the set has one row?
A set of one is still a set, and a formula column holds one value per row. Predecessors[Finish date] is a list even on a task with one predecessor. ONLY says you expect one; MAX works with one or ten.
What do the errors say?
"ONLY: the set is empty" on rows with no row in the set, and "ONLY: the set has more than one row" on rows with several.