Description
Use this function to reference the specified range to be considered for each child sheet in a Workiva spreadsheet.
Note: This function is not found in Excel, and only works inside a "parent" function.
Syntax
CHILDREFS(criterion)
Inputs
This function has the following arguments:
| Name | Required | Valid input |
|---|---|---|
criterion |
Yes | A number, expression, cell reference, or text string that identifies what is to be considered. |
Supported functions
The following functions can be used inside of the CHILDREFS function:
| AND | LARGE (for argument 1) | RANK.EQ (for argument 2) |
| AVERAGE | MAX | RANK.AVG (for argument 2) |
| AVERAGEA | MAXA | SMALL (for argument 1) |
| CHOOSE (for all arguments other than argument 1) | MEDIAN | STDEV |
| CONCATENATE | MIN | STDEV.P |
| COUNT | MINA | STDEV.S |
| COUNTA | NPV (for all arguments other than argument 1) | STDEVA |
| COUNTBLANK | OR | STDEVPA |
| IF (for arguments 2 and 3) | PRODUCT | SUM |
| IFS (for the even arguments: 2, 4, 6, …) | RANK (for argument 2) | TEXTJOIN (for all arguments other than argument 2) |
Example
Sample data
The following data is a single Workiva spreadsheet containing three child sheets:
Parent
Top-level sheet (this is the sheet that will have cells containing the CHILDREFS formulas)
| A | B |
|---|---|
| Sum of all the B1 cells | $15035.47 |
| Largest value | $11037.93 |
| Smallest value | $662.85 |
North
| A | B |
|---|---|
| Toronto, Canada | $2515.27 |
| Chicago, USA | $7251.48 |
| Montreal, Canada | $2182.43 |
| Boston, USA | $1296.56 |
| Minneapolis, USA | $662.85 |
South
| A | B |
|---|---|
| Miami, USA | $9287.65 |
| New Orleans, USA | $8981.35 |
| Atlanta, USA | $11037.93 |
| Houston, USA | $6944.6 |
| Mexico City, Mexico | $4278.78 |
West
| A | B |
|---|---|
| Los Angeles, USA | $3232.55 |
| Vancouver, Canada | $4380.67 |
| Seattle, USA | $5351.47 |
| Phoenix, USA | $4352.46 |
| Denver, USA | $3777.13 |
Sample formulas
| Use case | Formula | Description |
|---|---|---|
| Add together the values found in the B1 cell in the child sheets. | =SUM(CHILDREFS(B1)) |
This formula adds all the values found in the B1 cell in the child sheets. For this data set, this formula returns: 15035.47 |
| Identify the largest value in the B column cells in the child sheets. | =LARGE(CHILDREFS(B:B),1) |
This formula finds the largest value in the B column cells in the child sheets. For this data set, this formula returns: 11037.93 ("Atlanta" on the South sheet). |
| Identify the smallest value in the B column cells in the child sheets. | =SMALL(CHILDREFS(B:B),1) |
This formula finds the smallest value in the B column cells in the child sheets. For this data set, this formula returns: 662.85 ("Minneapolis" on the North sheet). |
Additional information
- Wildcards don't work with this function.
- You can include the CHILDREFS function more than once in a formula. This enables you to compound aggregations from different parent sheets all in one formula.
If a CHILDREFS formula doesn't include all the expected values
Using the formula =SUM(CHILDREFS(A6)) as an example, if it is not capturing all the children, here are probable reasons why this may be so:
The "grandchild" limitation
CHILDREFS only looks at the sheets exactly one level down in the hierarchy (direct children).
Scenario: If Sheet A has a child Sheet B, and Sheet B has a child Sheet C, a CHILDREFS formula on Sheet A will only pull from Sheet B. It will completely ignore Sheet C.
To fix: Ensure all sheets to be considered are nested only one level deep. To include "grandchildren", you will need to roll data up into the upper sheets first.
Sheet promotion/demotion
Since the function is dynamic, any change to the spreadsheet structure instantly changes the result.
Scenario: If a sheet was accidentally "promoted" (moved to the left in the outline), or "demoted" (moved to be two levels below the parent) it is no longer considered a child and is excluded.
To check for this: Look at the Outline (the left-hand panel). Any sheet that is not physically indented by one level under the parent sheet will be ignored by the formula.
To fix: Ensure that all the sheets to be considered are at the same single level below the parent.
Non-contiguous sheets
CHILDREFS only works for sheets that are nested directly under the parent.
Scenario: Sheets grouped using "Folder" sections in the outline will cause the function to break at the folder.
To fix: Ensure that the parent sheet and the children are not separated by a folder, or roll the data up into the appropriate cell in the folder.
Data type or content mismatch
Even if the sheet is a child, the SUM part of the formula will skip the cell if it doesn't recognize the value as a number.
To check for this: Check to see if the cells in the child sheets (in this example, cell A6 in those sheets) are not formatted as Text or contain hidden characters (such as a space or a ' prefix).
To fix: Correct the formatting or remove the problem character.
The "Zero" Issue: If a child sheet is restricted (the user doesn't have permissions to it) or the cell is blank, it won't throw an error; it will just contribute 0 to the sum.
Hidden or filtered sheets
While CHILDREFS generally includes all children, if a sheet is filtered out of a specific view or has "Exclusion" properties in certain advanced reporting settings, it can occasionally cause discrepancies.
To check for this: Temporarily change the formula to =COUNT(CHILDREFS(A6)).
If the count is 5 but they expect 8 children, the issue is the Outline Hierarchy (Item 1 or 2).
If the count is 8 but the SUM is lower than expected, the issue is the Data/Formatting in the child cells (Item 4).
To fix: Correct the issue using the solutions presented above.