Compare CHILD cell value in a reference formula
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.
Thanks everyone for your help in advance?
Best Answer
-
Instead of
CHILD([Pending Decisions]@row
use
Category@row
Answers
-
Do you have a [Pending Decisions] column in your metrics sheet where the formula lives?
-
@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.
-
Right. But do you have a column actually called "Pending Decisions"?
-
@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?
-
Instead of
CHILD([Pending Decisions]@row
use
Category@row
-
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.
-
But your formula is looking at the Status in the source sheet to determine which status to pull the count for.
-
@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!
-
Happy to help. 👍️
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.6K Get Help
- 403 Global Discussions
- 214 Industry Talk
- 454 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 136 Just for fun
- 56 Community Job Board
- 456 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 296 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!