# Compare CHILD cell value in a reference formula

Options
✭✭✭✭

Hello, I have two sheets: a tracker sheet and a metrics sheet. In the metrics sheet, I am trying to calculate a variety of metric values where I need to match a CHILD cell value to a value referenced in the tracker sheet.

Here is the current formula that works as is

=COUNTIFS({Activity}, "Decision", {University}, Category25, {Status}, "Pending", {TD - SD}, >=0, {TD - SD}, <30)

Here is the formula where I am trying to substitute CHILD for Category25 that returns an #UNPARSEABLE error

=COUNTIFS({Activity}, "Decision", {University}, CHILD([Pending Decisions]@row, {Status}, "Pending", {TD - SD}, >=0, {TD - SD}, <30)

Here is a screenshots of my Tracker sheet where the University column is referenced in the formula as {University}

Here is a screenshot of the Metrics sheet

I am trying, in the formula in the Last 30 Days column, to match Tracker sheet, University column value of "Averett" to the metric sheet PARENT Pending Decisions, CHILD value of Averett.

• ✭✭✭✭✭✭
Options

CHILD([Pending Decisions]@row

use

Category@row

• ✭✭✭✭✭✭
Options

Do you have a [Pending Decisions] column in your metrics sheet where the formula lives?

• ✭✭✭✭
Options

@Paul Newcome - yes, I have a Status column that is out of the screenshot that contains either Pending, Closed in Agreement, or Closed in Dispute. All parts of the first formula work. All part of the second formula work and the only changed values are the ones I bolded. I have tried ANCESTER and CHILD and I get the same error on both.

• ✭✭✭✭✭✭
Options

Right. But do you have a column actually called "Pending Decisions"?

• ✭✭✭✭
Options

@Paul Newcome - no, Pending Decisions is the name of the Parent Row (of the Category column) that the Averett child falls under. The formula lives in the Count column indicated by the red box - both of which are demonstrated in the 2nd screenshot of the Metrics Sheet. Does that make sense?

• ✭✭✭✭✭✭
Options

CHILD([Pending Decisions]@row

use

Category@row

• ✭✭✭✭
Options

Thank you for looking at this with me Paul. The issue with Category@row is I have more than one PARENT section that contains the name of each university. For example, I have a PARENT section called University which contains each university and I have a PARENT section called Pending Decisions which contains each university so, in this example, Averett is a row of data in each PARENT section. I currently use Category@row in the first section, University, and it works great but when I get to the next section, Pending Decision, I need it to find the Averett row in the second section which is why I was trying to figure out how to use PARENT, CHILD, ANCESTOR, etc.

• ✭✭✭✭✭✭
Options

But your formula is looking at the Status in the source sheet to determine which status to pull the count for.

• ✭✭✭✭
Options

@Paul Newcome - oh, duh?? My apologies. I was thinking category@row was going to pull the first match it encountered but it always pulls the "category" from the "@row" where it exists. I was able to confirm this through testing my changing the "Averett" in each row to "Averett1" where I could see the remaining rows returning the proper calculated values. Thank you!

• ✭✭✭✭✭✭
Options

Happy to help. 👍️

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!