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 |
C |
|
|---|---|---|---|
1 |
Sum of all the B1 cells |
$15035.47 |
|
2 |
Largest value |
$11037.93 |
|
3 |
Smallest value |
$662.85 |
North
A |
B |
C |
|
|---|---|---|---|
1 |
Toronto, Canada |
$2515.27 |
|
2 |
Chicago, USA |
$7251.48 |
|
3 |
Montreal, Canada |
$2182.43 |
|
4 |
Boston, USA |
$1296.56 |
|
5 |
Minneapolis, USA |
$662.85 |
South
A |
B |
C |
|
|---|---|---|---|
1 |
Miami, USA |
$9287.65 |
|
2 |
New Orleans, USA |
$8981.35 |
|
3 |
Atlanta, USA |
$11037.93 |
|
4 |
Houston, USA |
$6944.6 |
|
5 |
Mexico City, Mexico |
$4278.78 |
West
A |
B |
C |
|
|---|---|---|---|
1 |
Los Angeles, USA |
$3232.55 |
|
2 |
Vancouver, Canada |
$4380.67 |
|
3 |
Seattle, USA |
$5351.47 |
|
4 |
Phoenix, USA |
$4352.46 |
|
5 |
Denver, USA |
$3777.13 |
Sample formulas
| Use case | Formula | Explanation and Result |
|---|---|---|
| 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.