How to pull most recent test result value from children cells

Caitlin O

I am working on pulling most recent values of children cells into a parent row across multiple columns. I can get it to work for my test iteration number and test execution date, but not for the test result which is part of a dropdown list. The formula I am using (replacing the reference column as I go) is:

=IF(COUNT(CHILDREN()) > 0, INDEX(CHILDREN(), MATCH(MAX(CHILDREN([Test Iteration]@row)), CHILDREN([Test Iteration]@row))))

I am attaching a screenshot to show the #no match error I am getting in the test result column instead of the "pass" value I am wanting to see.

Genevieve P.

Hi @Caitlin O

The first thing I would test is try adding a 0 at the end of your MATCH Function. This tells it to search each cell in the Range, and not presume a certain sort order:

=IF(COUNT(CHILDREN()) > 0, INDEX(CHILDREN(), MATCH(MAX(CHILDREN([Test Iteration]@row)), CHILDREN([Test Iteration]@row), 0)))

If this hasn't helped, can you confirm how you're getting the Test Iteration value? It's possible that the formula outputting this number is adjusting the value to be seen as text, whereas the actual number is numerical. This could result in a NO MATCH error.

Let me know if the first adjustment did the trick!

Cheers,

Genevieve

Caitlin O

Thanks @Genevieve P. but putting a 0 in did not work. The test iteration value is being put in manually, and the test result is also manual from a dropdown list.

Genevieve P.

Hi @Caitlin O

Thank you for confirming this! Can you try breaking down the formula, so we can identify where the error is occurring? Test each part separately:

=COUNT(CHILDREN())

=MAX(CHILDREN([Test Iteration]@row))

=INDEX(CHILDREN(), MATCH(3, CHILDREN([Test Iteration]@row))

One other thing to check is to see if there are any formula errors in either of these columns. If there's a formula error somewhere else in the column it can sometimes create a domino effect and surface a formula error here as well.

Let me know what the results were!

Caitlin O

Thank you @Genevieve P.! the COUNT and INDEX functions worked as expected, but the following gave an #UNPARSEABLE error:

=MAX(CHILDREN([Test Iteration]@row))

Genevieve P.

Could you post a screen capture of how the formula shows when you type it in, including the column name? Like so:

Based on your image above you shouldn't be receiving unparseable, since the syntax is correct and the column name is spelled the same.

Caitlin O

@Genevieve P.

I must have typed the MAX function in wrong at first because it worked when I tried to get the screenshot.

So I copied and pasted my formula from my original question back into the test result parent cell...and it worked. I dragged it into another column to the right and got the #NO MATCH error again, so I deleted it and copy-pasted from my original question here again and that worked. That doesn't make sense to me because it's the exact same formula, but I'm rolling with it. Thank you for your help.

Genevieve P.

Hi @Caitlin O

That actually helps! When you copy/paste is pastes in the exact formula. However, when you drag a formula over, it auto-updates the column names that are being referenced to move with the formula. This would change CHILDREN([Test Iteration]@row) to look at the children in a different column, perhaps one that couldn't be used as a MAX, or one that doesn't have a Match.

In any case, I'm glad it works now!