Skip to content

Smartsheet Change Register#

Column Summary#

Title Brief Description
Activity Contingency %   The amount to markup the activity. % to be applied. Do not populate unless the markup for a specific item differs from the default set in the Sheet Summary.
Activity Expense Markup   The amount to markup the expense. % to be applied. Do not populate unless the markup for a specific item differs from the default set in the Sheet Summary.
Activity Expense Type   Select from standard expense types, or to enter a custom expense amount type a value in the form "$xx - Description of Expense" (description is optional but you must include the hyphen).
Activity Expense Unit Cost   Indicates the cost per unit of the expense.
Activity Expense Quantity   Indicates the quantity amount of the expense.
Activity Labour (hrs)   The calculated number of hours required for the task.
Activity Labour Rate ($/hr)   The activity labour rate for the task.
Activity Lump Sum Expense ($)
Activity Lump Sum Labour ($)
Activity Lump Sum Procurement ($)
Activity Lump Sum Total ($)
Activity Reimbursable Expense ($)
Activity Reimbursable Labour ($)
Activity Reimbursable Procurement ($)
Activity Reimbursable Total ($)
Activity Procurement Markup   The amount to markup the procurement. % to be applied. Do not populate unless the markup for a specific item differs from the default set in the Sheet Summary.
Activity Procurement Unit Cost   The cost per unit of the procurement item.
Activity Procurement Quantity   Indicates the amount of procurement quantity.
Applicable Activity Expense ($)
Applicable Activity Labour ($)
Applicable Activity Procurement ($)
Applicable Activity Total ($)
Applicable Expense ($)
Applicable Labour ($)
Applicable Procurement ($)
Applicable Total ($)   The calculated total dollar amount for this task.
Apply Conditional Formatting?
Approved Date    The date the change notice / variation was approved
Budget Assigned [hrs] A budget in hours for the specific schedule activity if the estimate migrates to a schedule
Budget Assigned [$] A budget in dollars for the specific schedule activity if the estimate migrates to a schedule
Cancelled Date    The date the change notice / variation was cancelled
Change #   The change notice number.
Change Status   The status of the change notice / variation.
Cost Activity   The cost activity that will show on the schedule and Avaza
Duration Duration in days assigned to each cost item. This can be entered or auto-calculated based on the Start and End dates.
Estimate Resource Loading
Finish Date The anticipated finish date for each cost item.
Information   Any information that is required to be noted.
Labour (hrs)   The labour hours required for the task.
Line Item Type
Location   The location of the resource cateogry used: 'Site' or 'Office'.
Lump Sum Expense ($)
Lump Sum Labour ($)
Lump Sum Procurement ($)
Lump Sum Total ($)
New Activity Labour Rate ($/hr)   The new activity labour rate for the task. To be manually entered if the existing data from Estimate Data - Roles & Rates is incorrect.
New Activity Name   The description of the new activity.
Non-Standard Capacity Standard resource capacity is 8hrs/day. Set this value where the activity is, for example, performed on site and involves 12hr days not standard 8hr office days (in this case type '12
PO   The purchase order number.
PO Grouping   The grouping of the purchase order pulled from the 1000_PO_Register. Indicates labour or equipment if valid.
PO Grouping Value (Incl Variation)   The total $ dollar value overview of the purchase order grouping including variation
PO Value (Incl Variation)   The total $ dollar value of the purchase order including variation
Predecessors
Project
Project ID
Qty   Indicates the quantity amount needed for the task.
Reimbursable?   Indicates whether or not the activity is reimbursable or not. Used in calculating contingency if lump sum.
Reimbursable Activity
Reimbursable Expense ($)
Reimbursable Labour ($)
Reimbursable Procurement ($)
Reimbursable Total ($)
Resource(s)   The resources identified to be allocated to the task.
Resource Role   The resource category to be used for the task.
Scheduled Item?
Submitted Date    The date the change notice / variation was submitted
Start Date The anticipated start date for each cost item.
Time (Hrs)   Indicates the amount of hours required for each quantity of the task.
Total Approved Budget [$]   The total approved budget dollars when in the 'approved' status.
Total Approved Budget [hrs]   The total approved budget hours when in the 'approved' status.
Total Requested Budget [$]   The total requested budget dollars when in the 'submitted' status.
Total Requested Budget [hrs]   The total requested budget hours when in the 'submitted' status.
Total Change Value [$]   Indicates the dollar value of all the changes irrespective of the status
Total Change Value [hrs]   Indicates the hours value of all the changes irrespective of the status
Variation #   The variation request number.
Varied Date    The date the change notice / variation was received / varied
Variation Status for Activity   The status of the variation. Formula to indicate if variation is standalone or not.
WBS Descendants
WBS Level

Column Details#

Activity Expense Type#

Dropdown

- Drug and Alcohol Screen
- Flights PER/KTA
- Flights PER/PHE
- GTE PHE Car Hire
- Hire Car KTA (/day)
- Hire Car PHE (/day)
- Induction Medical - BHP
- Induction Medical - CPPC
- Meal while on site (per day)
- MSIC - 2 Years Only
- Port Hedland Accom (MIAMIA)
- Taxi (One Way)

Enter a custom expense amount type and a value in the form:

$xx - Description of Expense

Activity Expense Unit Cost#

Formula

=IF(
    [Activity Expense Type]@row = "", 
    "", 
    IF(
        ISERROR(
            MATCH(
                [Activity Expense Type]@row, 
                {Expenses - Type},
                0
            )
        ), 
        VALUE(
            SUBSTITUTE(
                SUBSTITUTE(
                    LEFT(
                        Activity Expense Type]@row, 
                        MAX(
                            FIND(
                                "-", 
                                [Activity Expense Type]@row
                            ) 
                            - 1, 
                            0
                        )
                    ), 
                    "$", 
                    ""
                ), 
                " ", 
                ""
            )
        ), 
        INDEX(
            {Expenses - Unit Cost}, 
            MATCH(
                [Activity Expense Type]@row, 
                {Expenses - Type}, 
                0
            )
        )
    )
)

Activity Expense Quantity#

Formula

=IF(
    [Activity Expense Type]@row <> "", 
    Qty@row, 
    ""
)

Activity Labour (hrs)#

Formula

=IF(
    [Time (Hrs)]@row <> "", 
    Qty@row * [Time (Hrs)]@row, 
    ""
)

Activity Labour Rate ($/hr)#

Formula

=IF(
    [New Activity Labour Rate ($/hr)]@row <> "",
    [New Activity Labour Rate ($/hr)]@row, 
    IF(
        [Activity Labour (hrs)]@row = "",
        "", 
        IF(
            OR(
                Client# = "CPPC",
                Client# = "CPM"
            ),
            IF(
                Location@row = "Office",
                INDEX({Roles & Rates - CPPC/CPM_Office}, MATCH([Resource Role]@row, {Roles & Rates - Role}, 0)),
                INDEX({Roles & Rates - CPPC/CPM_Site}, MATCH([Resource Role]@row, {Roles & Rates - Role}, 0))
            ),
            IF(
                Location@row = "Office",
                INDEX({Roles & Rates - Standard_Office}, MATCH([Resource Role]@row, {Roles & Rates - Role}, 0)),
                INDEX({Roles & Rates - Standard_Site}, MATCH([Resource Role]@row, {Roles & Rates - Role}, 0))
            )
        )
    )
)

Activity Lump Sum Expense ($)#

Formula

=IF(
    [Activity Reimbursable Expense ($)]@row = "", 
    "", 
    IF(
        [Activity Contingency %]@row <> "", 
        [Activity Reimbursable Expense ($)]@row * (1 + [Activity Contingency %]@row), 
        [Activity Reimbursable Expense ($)]@row * (1 + [Default Contingency (%) \[if applic.\]]# / 100)
    )
)

Activity Lump Sum Labour ($)#

Formula

=IF(
    [Activity Reimbursable Labour ($)]@row = "", 
    "", 
    IF(
        [Activity Contingency %]@row <> "", 
        [Activity Reimbursable Labour ($)]@row * (1 + [Activity Contingency %]@row), 
        [Activity Reimbursable Labour ($)]@row * (1 + [Default Contingency (%) \[if applic.\]]# / 100)
    )
)

Activity Lump Sum Procurement ($)#

Formula

=IF(
    [Activity Reimbursable Procurement ($)]@row = "", 
    "", 
    IF(
        [Activity Contingency %]@row <> "", 
        [Activity Reimbursable Procurement ($)]@row * (1 + [Activity Contingency %]@row), 
        [Activity Reimbursable Procurement ($)]@row * (1 + [Default Contingency (%) \[if applic.\]]# / 100)
    )
)

Activity Lump Sum Total ($)#

Formula

=IF(
    [Change Status]@row = "BASE SCOPE", 
    "", 
    IF(
        [Activity Lump Sum Labour ($)]@row <> "", 
        [Activity Lump Sum Labour ($)]@row,
        0
    ) + 
    IF(
        [Activity Lump Sum Expense ($)]@row <> "", 
        [Activity Lump Sum Expense ($)]@row, 
        0
    ) + 
    IF(
        [Activity Lump Sum Procurement ($)]@row <> "", 
        [Activity Lump Sum Procurement ($)]@row, 
        0
    )
)

Activity Procurement Quantity#

Formula

=IF(
    [Activity Procurement Unit Cost]@row <> "", 
    Qty@row
)

Activity Reimbursable Expense ($)#

Formula

=IF(
    AND(
        [Activity Expense Quantity]@row <> "",
        [Activity Expense Unit Cost]@row <> ""
    ), 
    [Activity Expense Quantity]@row * 
    [Activity Expense Unit Cost]@row * 
    (
        1 + IF(
        [Activity Expense Markup]@row <> "", 
        [Activity Expense Markup]@row, 
        [Expense Markup (%)]# / 100
        )
    ), 
    ""
)

Activity Reimbursable Labour ($)#

Formula

=IF(
    AND(
        ISNUMBER([Activity Labour (hrs)]@row), 
        ISNUMBER([Activity Labour Rate ($/hr)]@row)
    ), 
    [Activity Labour (hrs)]@row * [Activity Labour Rate ($/hr)]@row, 
    ""
)

Activity Reimbursable Procurement ($)#

Formula

=IF(
    AND(
        [Activity Procurement Quantity]@row <> "", 
        [Activity Procurement Unit Cost]@row <> ""
    ), 
    [Activity Procurement Quantity]@row * 
    [Activity Procurement Unit Cost]@row * 
    (
        1 + IF(
            [Activity Procurement Markup]@row <> "", 
            [Activity Procurement Markup]@row, 
            [Procurement Markup (%)]# / 100
        )
    ), 
    ""
)

Activity Reimbursable Total ($)#

Formula

=IF(
    [Change Status]@row = "BASE SCOPE", 
    "", 
    VALUE([Activity Reimbursable Labour ($)]@row) + 
    VALUE([Activity Reimbursable Expense ($)]@row) + 
    VALUE([Activity Reimbursable Procurement ($)]@row)
)

Applicable Activity Expense ($)#

Formula

=IF(
    [Line Item Type]@row = "Rates Based", 
    [Activity Reimbursable Expense ($)]@row, 
    [Activity Lump Sum Expense ($)]@row
)

Applicable Activity Labour ($)#

Formula

=IF(
    [Line Item Type]@row = "Rates Based", 
    [Activity Reimbursable Labour ($)]@row, 
    [Activity Lump Sum Labour ($)]@row
)

Applicable Activity Procurement ($)#

Formula

=IF(
    [Line Item Type]@row = "Rates Based", 
    [Activity Reimbursable Procurement ($)]@row, 
    [Activity Lump Sum Procurement ($)]@row
)

Applicable Activity Total ($)#

Formula

=IF(
    [Change Status]@row = "BASE SCOPE", 
    "", 
    VALUE([Applicable Activity Labour ($)]@row) + 
    VALUE([Applicable Activity Expense ($)]@row) + 
    VALUE([Applicable Activity Procurement ($)]@row)
)

Applicable Expense ($)#

Formula

=IF(
    COUNT(CHILDREN(Activity@row)) > 0, 
    SUM(CHILDREN()), 
    IF(
        [Line Item Type]@row = "Rates Based", 
        [Reimbursable Expense ($)]@row, 
        [Lump Sum Expense ($)]@row
    )
)

Applicable Labour ($)#

Formula

=IF(
    COUNT(CHILDREN(Activity@row)) > 0, 
    SUM(CHILDREN()), 
    IF(
        [Line Item Type]@row = "Rates Based", 
        [Reimbursable Labour ($)]@row, 
        [Lump Sum Labour ($)]@row
    )
)

Applicable Procurement ($)#

Formula

=IF(
    COUNT(CHILDREN(Activity@row)) > 0, 
    SUM(CHILDREN()), 
    IF(
        [Line Item Type]@row = "Rates Based", 
        [Reimbursable Procurement ($)]@row, 
        [Lump Sum Procurement ($)]@row
    )
)

Applicable Total ($)#

Formula

=IF(
    [Change Status]@row = "BASE SCOPE", 
    "", 
    VALUE([Applicable Labour ($)]@row) + 
    VALUE([Applicable Expense ($)]@row) + 
    VALUE([Applicable Procurement ($)]@row)
)

Change#

Formula

=IF(
    [WBS Level]@row <= 2, 
    [Change #]@row, 
    PARENT()
)

Change Status#

Dropdown

- Draft
- CHN Submitted
- CHN Cancelled
- CHN Approved
- VAR Submitted
- VAR Cancelled
- VAR Received
- BASE SCOPE

Cost Activity#

Formula

=IF(
    [WBS Level]@row = 1,
    IF(
        [Project ID]# <> "xxxx",
        "P" + [Project ID]# + " | " + [Project Name]#,
        "SETUP REQ'D: OPEN SHEET SUMMARY"
    ), 
    IF(
        [WBS Level]@row = 2, 
        [Change #]@row + IF([Change #]@row <> "", " - ", "") + [New Activity Name]@row,
        [New Activity Name]@row
    )
)

Estimate Resource Loading#

Formula

=IF(
    AND(
        [Variation Status for Activity]@row <> "VAR Received", 
        NOT(CONTAINS("Cancelled", [Variation Status for Activity]@row)
        )
    ), 
    IFERROR(
        ([Labour (hrs)]@row / Duration@row) / 
        (
            COUNTM([Resource(s)]@row) * 
            IF(
                [Non-Standard Capacity]@row <> "", 
                [Non-Standard Capacity]@row, 8
            )
        ) * 
        IF(
            [Resource Loading Factor]# > 0, 
            [Resource Loading Factor]#,
            1
        ), 
        0
    ), 
    0
)

Lump Sum Total ($)#

Formula

=IF(
    [Change Status]@row = "BASE SCOPE", 
    "", 
    IF(
        AND(
            [Activity Lump Sum Total ($)]@row <> "", 
            [Activity Lump Sum Total ($)]@row <> 0
        ), 
        [Activity Lump Sum Total ($)]@row, 
        SUM(CHILDREN([Fixed Price Total ($)]@row))
    )
)

Labour (hrs)#

Formula

=IF(
    AND(
        [Activity Labour (hrs)]@row <> "", 
        [Activity Labour (hrs)]@row <> 0
    ), 
    [Activity Labour (hrs)]@row, 
    SUM(
        CHILDREN(
            [Labour (hrs)]@row
        )
    )
)
+ 
IF(
    [Cost Activity]@row = "BASE SCOPE", 
    SUM(
        COLLECT(
            {PO Register - PO Value At Project Award [hrs]}, 
            {PO Register - Reference}, 
            PO@row, 
            {PO Register - Project ID}, 
            [Project ID]@row
        )
    ), 
    0
)

Line Item Type#

Dropdown

=IF(
    [WBS Descendants]@row = 0, 
    IF(
        [Proposal Type (for Display Only)]# = "Rates Based", 
        "Rates Based", 
        IF(
            [Reimbursable Activity]@row = 1, 
            "Rates Based", 
            "Lump Sum"
        )
    ), 
    IF(
        COUNT(DISTINCT(DESCENDANTS())) = 2, 
        "Lump Sum + Rates", 
        IF(
            CONTAINS("Rates Based", DISTINCT(DESCENDANTS())), 
            "Rates Based", 
            "Lump Sum"
        )
    )
)

Location#

Dropdown

- Office
- Site

Lump Sum Expense ($)#

Formula

=IF(
    [Change Status]@row = "BASE SCOPE", 
    "", 
    IF(
        AND(
            [Activity Lump Sum Expense ($)]@row <> "", 
            [Activity Lump Sum Expense ($)]@row <> 0
        ), 
        [Activity Lump Sum Expense ($)]@row, 
        SUM(CHILDREN([Lump Sum Expense ($)]@row))
    )
)

Lump Sum Labour ($)#

Formula

=IF(
    [Change Status]@row = "BASE SCOPE", 
    "", 
    IF(
        AND(
            [Activity Lump Sum Labour ($)]@row <> "", 
            [Activity Lump Sum Labour ($)]@row <> 0
        ), 
        [Activity Lump Sum Labour ($)]@row, 
        SUM(CHILDREN([Lump Sum Labour ($)]@row))
    )
)

Lump Sum Procurement ($)#

Formula

=IF(
    [Change Status]@row = "BASE SCOPE", 
    "", 
    IF(
        AND(
            [Activity Lump Sum Procurement ($)]@row <> "", 
            [Activity Lump Sum Procurement ($)]@row <> 0
        ), 
        [Activity Lump Sum Procurement ($)]@row, 
        SUM(CHILDREN([Lump Sum Procurement ($)]@row))
    )
)

Lump Sum Total ($)#

Formula

=IF(
    [Change Status]@row = "BASE SCOPE", 
    "", 
    IF(
        AND(
            [Activity Lump Sum Total ($)]@row <> "", 
            [Activity Lump Sum Total ($)]@row <> 0
        ), 
        [Activity Lump Sum Total ($)]@row, 
        SUM(CHILDREN([Fixed Price Total ($)]@row))
    )
)

PO Grouping#

Formula

=IFERROR(
    INDEX(
        COLLECT(
            {PO Register - PO Grouping}, 
            {PO Register - Reference}, 
            PO@row, {PO Register - Project ID}, 
            [Project ID]@row
        ), 
        1
    ), 
    "Default"
)

PO Grouping Value (Incl Variation)#

Formula

=IFERROR(
    SUM(
        COLLECT(
            {PO_Register - Order Net Value}, 
            {PO_Register - PO Grouping}, 
            [PO Grouping]@row, 
            {PO_Register - Project ID}, 
            [Project ID]@row
        )
    ), 
    ""
)

PO Value (Incl Variation)#

Formula

=IF(
    [Change Status]@row = "BASE SCOPE", 
    SUM(
        COLLECT(
            {PO Register - Order Net Value}, 
            {PO Register - Reference}, 
            PO@row, 
            {PO Register - Project ID}, 
            [Project ID]@row
        )
    ), 
    ""
)

Project#

Formula

=IF(
    [Project ID]# <> "xxxx", 
    "P" + [Project ID]# + " - " + [Project Name]#, 
    ""
)

Project ID#

Formula

=[Project ID]#

Reimbursable Expense ($)#

Formula

=IF(
    [Change Status]@row = "BASE SCOPE", 
    "", 
    IF(
        AND(
            [Activity Reimbursable Expense ($)]@row <> "", 
            [Activity Reimbursable Expense ($)]@row <> 0
        ), 
        [Activity Reimbursable Expense ($)]@row, 
        SUM(CHILDREN([Reimbursable Expense ($)]@row))
    )
)

Reimbursable Labour ($)#

Formula

=IF(
    [Change Status]@row = "BASE SCOPE", 
    "", 
    IF(
        AND(
            [Activity Reimbursable Labour ($)]@row <> "", 
            [Activity Reimbursable Labour ($)]@row <> 0
        ), 
        [Activity Reimbursable Labour ($)]@row, 
        SUM(CHILDREN([Reimbursable Labour ($)]@row))
    )
)

Reimbursable Procurement ($)#

Formula

=IF(
    [Change Status]@row = "BASE SCOPE", 
    "", 
    IF(
        AND(
            [Activity Reimbursable Procurement ($)]@row <> "", 
            [Activity Reimbursable Procurement ($)]@row <> 0
        ), 
        [Activity Reimbursable Procurement ($)]@row, 
        SUM(CHILDREN([Reimbursable Procurement ($)]@row))
    )
)

Reimbursable Total ($)#

Formula

=IF(
    [Change Status]@row = "BASE SCOPE", 
    "", 
    IF(
        AND(
            [Activity Reimbursable Total ($)]@row <> "", 
            [Activity Reimbursable Total ($)]@row <> 0
        ), 
        [Activity Reimbursable Total ($)]@row, 
        SUM(CHILDREN([Reimbursable Total ($)]@row))
    )
)

Resource Role#

Dropdown

- Admin/Doc Control
- Application Engineer
- Controls Engineer L1
- Controls Engineer L2
- Draftsperson
- Electrical Supervisor
- Electrician
- Electrical Engineer L1
- Electrical Engineer L2
- Engineering Manager
- Functional Safety Engineer L1
- Functional Safety Engineer L2
- Functional Safety Expert
- Graduate Engineer
- Graduate Engineer Half Cost
- IT Specialist
- Lead Controls Engineer L1
- Lead Controls Engineer L2
- Lead Designer
- Lead Electrical Engineer L1
- Lead Electrical Engineer L2
- Lead Electrician
- Lead Network Engineer L1
- Lead Network Engineer L2
- Network EngineerL1
- Network EngineerL2
- PH Lead Controls Engineer
- PH Controls Engineer
- PH Software Developer
- Principal Controls Engineer
- Principal Network Engineer
- Project Engineer
- Project Director
- Project Manager
- SCADA SME
- Senior Controls Engineer L1
- Senior Controls Engineer L2
- Senior Designer L1
- Senior Designer L2
- Senior Electrical Engineer L1
- Senior Electrical Engineer L2
- Senior Network Engineer L1
- Senior Network Engineer L2
- Senior Project Manager
- Specialist Drives Engineer
- Technical Assistant
- Technical Officer
- Technical Writer
- Vacation Student Engineer

Time (Hrs)#

Formula

=INDEX(
    {Project Register - Total Awarded Budget [hrs]},
    MATCH(
        [Project ID]#,
        {Project Register - Project ID},
        0
    )
)

Total Approved Budget [$]#

Formula

=IF(
    [WBS Level]@row = 1,
    SUM(CHILDREN([Total Approved Budget \[$\]]@row)), 
    IF(
        AND(
            [Applicable Total ($)]@row <> "",
            [Applicable Total ($)]@row <> 0, 
            OR(
                [Variation Status for Activity]@row = "CHN Approved", 
                [Variation Status for Activity]@row = "VAR Received"
            )
        ), 
        [Applicable Total ($)]@row, 
        IF(
            [Variation Status for Activity]@row = "BASE SCOPE", 
            SUM(
                COLLECT(
                    {PO Register – PO Value At Project Award [$]}, 
                    {PO Register - Reference}, [PO Reference]@row, 
                    {PO Register - Project ID}, [Project ID]@row
                )
            ), 
            SUMIF(
                CHILDREN([Change Status]@row), 
                OR(
                    @cell = "CHN Approved", 
                    @cell = "VAR Received"
                ), 
                CHILDREN([Applicable Total ($)]@row)
            )
        )
    )
)

Total Approved Budget [hrs]#

Formula

=IF(
    [WBS Level]@row = 1, 
    SUM(CHILDREN([Total Approved Budget \[hrs\]]@row)),
    IF(
        AND(
            [Labour (hrs)]@row <> "",
            [Labour (hrs)]@row <> 0,
            OR(
                [Variation Status for Activity]@row = "CHN Approved",
                [Variation Status for Activity]@row = "VAR Received"
            )
        ),
        [Labour (hrs)]@row, 
        IF(
            [Variation Status for Activity]@row = "BASE SCOPE", 
            SUM(
                COLLECT(
                    {PO Register - PO Value At Project Award [hrs]},
                    {PO Register - Reference}, [PO Reference]@row, 
                    {PO Register - Project ID}, [Project ID]@row
                )
            ), 
            SUMIF(
                CHILDREN([Variation Status for Activity]@row), 
                OR(
                    @cell = "CHN Approved", 
                    @cell = "VAR Received"
                ), 
                CHILDREN([Labour (hrs)]@row)
            )
        )
    )
)

Total Change Value [$]#

Formula

=[Applicable Total ($)]@row

Total Change Value [hrs]#

Formula

=[Labour (hrs)]@row

Total Requested Budget [$]#

Formula

=IF(
    AND(
        [Applicable Activity Total ($)]@row <> "", 
        [Applicable Activity Total ($)]@row <> 0, 
        OR(
            [Variation Status for Activity]@row = "CHN Submitted", 
            [Variation Status for Activity]@row = "VAR Submitted"
        )
    ), 
    [Applicable Activity Total ($)]@row, 
    SUMIF(
        CHILDREN([Variation Status for Activity]@row), 
        OR(
            @cell = "CHN Submitted", 
            @cell = "VAR Submitted"
        ), 
        CHILDREN([Applicable Total ($)]@row)
    )
)

Total Requested Budget [hrs]#

Formula

=IF(
    AND(
        [Activity Labour (hrs)]@row <> "", 
        [Activity Labour (hrs)]@row <> 0, 
        OR(
            [Variation Status for Activity]@row = "CHN Submitted", 
            [Variation Status for Activity]@row = "VAR Submitted"
        )
    ), 
    [Activity Labour (hrs)]@row, 
    SUMIF(
        CHILDREN([Variation Status for Activity]@row), 
        OR(
            @cell = "CHN Submitted", 
            @cell = "VAR Submitted"
        ), 
        CHILDREN([Labour (hrs)]@row)
    )
)

Variation Status for Activity#

Formula

=IF(
    [WBS Level]@row <= 2, 
    IF(
        AND(
            [Change Status]@row = "VAR Submitted", 
            CONTAINS(
                "CHN", 
                [Change #]@row
            )
        ), 
        "CHN Approved", 
        [Change Status]@row
    ), 
    PARENT()
)

WBS Descendants#

Formula

=COUNT(DESCENDANTS([WBS Level]@row))

WBS Level#

Formula

=COUNT(ANCESTORS()) + 1