How do I refer to specific child row.

Options

Hi, I need help to do some calculations on daily basis (columns 01 to 31) at each parent level . For e.g In below screenshot If I want to calculate difference between (a child rows) "Total Vulnerability count" - "Total New Vulnerability Count" every day using generic formula so that I can apply at each parent level. what is the best way to do it? Thanks in advance.



Best Answer

  • Genevieve P.
    Genevieve P. Employee Admin
    Answer ✓
    Options

    Hi @PKotha

    Try this:

    =INDEX(CHILDREN(), MATCH("total vulnerability count", CHILDREN($Application@row), 0)) - INDEX(CHILDREN(), MATCH("total new vulnerability count", CHILDREN($Application@row), 0))

    It searches within the children of this current row in the "Application" column to find the matching "value" and bring back the number from that row in the current column (e.g. 01), subtracting one from the other.

    I've put in an absolute reference ($) so you can drag this formula across the entire row and it will stay looking at your primary column for the "value".

    Let me know if this works for you!

    Cheers,

    Genevieve

Answers

  • Genevieve P.
    Genevieve P. Employee Admin
    Answer ✓
    Options

    Hi @PKotha

    Try this:

    =INDEX(CHILDREN(), MATCH("total vulnerability count", CHILDREN($Application@row), 0)) - INDEX(CHILDREN(), MATCH("total new vulnerability count", CHILDREN($Application@row), 0))

    It searches within the children of this current row in the "Application" column to find the matching "value" and bring back the number from that row in the current column (e.g. 01), subtracting one from the other.

    I've put in an absolute reference ($) so you can drag this formula across the entire row and it will stay looking at your primary column for the "value".

    Let me know if this works for you!

    Cheers,

    Genevieve

  • PKotha
    PKotha ✭✭✭
    Options

    Prefect, it worked. Thank you.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!