I need help using multiple functions, if statements and parent/child.

jambla
jambla ✭✭
edited 12/09/19 in Formulas and Functions

I have 3 columns that have information; Scene, Take and Revision.

The scene can be broken into sub-scene and sub-sub-scene etc... This column will always have a value.

Columns Take and Revision may or may not have a value.

I would like to add a fourth row called Full Name. This column will combine the information.

I was able to do this:

=LOWER("scn" + Scene1 + "_" + Take1 + "_" + Revision1)

The issue Im having there is the underscore (). If there isn't a value present I would need to omit the underscore. I was able to get IF(ISBLANK() working but not combines with my other statement: ( =LOWER("scn" + Scene1 + "_" + Take1 + "_" + Revision1) )

I was also not able to get the parent/child working as far as, if I have a scene 003 with sub-scene b and sub-sub-scene 5. I wasn't able to output scn003b5

Any guidance would be much appreciated!

smartsheet.png

Tags:

Comments

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭

    Hi Jambla,

    Try this.

    This is for the parent

    ="scn" + LOWER(JOIN(COLLECT(Scene@row:Revision@row; Scene@row:Revision@row; NOT(ISBLANK(@cell))); "_"))    

    The same version but with the below changes for your and others convenience.  

    ="scn" + LOWER(JOIN(COLLECT(Scene@row:Revision@row, Scene@row:Revision@row, NOT(ISBLANK(@cell))), "_"))

    This is for the children

    ="scn" + PARENT(Scene@row) + LOWER(JOIN(COLLECT(Scene@row:Revision@row; Scene@row:Revision@row; NOT(ISBLANK(@cell))); "_"))    

    The same version but with the below changes for your and others convenience.  

    ="scn" + PARENT(Scene@row) + LOWER(JOIN(COLLECT(Scene@row:Revision@row, Scene@row:Revision@row, NOT(ISBLANK(@cell))), "_"))

    Depending on your country you’ll need to exchange the comma to a period and the semi-colon to a comma.

    The formula can most certainly be even more refined but this will get you started I think.

    Did it work?

    Have a fantastic week!

    Best,

    Andrée Starå

    Workflow Consultant @ Get Done Consulting

    Image 2018-12-17 at 9.21.59 fm.png

    SMARTSHEET EXPERT CONSULTANT & PARTNER

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    W: www.workbold.com | E:[email protected] | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

  • jambla
    jambla ✭✭
    edited 12/17/18

    Hi Andrée,

    Just before you posted I tried this:

    =LOWER("bas_scn" + JOIN(ANCESTORS(Scene1)) + Scene1 + IF(ISBLANK(Take1), "", "_" + Take1) + IF(ISBLANK(Revision1), "", "_" + Revision1))

    It works perfectly with parent, children, children's children etc. but I think definitely not the most elegant way of doing it. :-p

    I will play with yours a bit.

    Thanks for help! Much appreciated!

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭

    Happy to help!

    There are many solutions to the same problem wink

    I'll save yours!

    Best,

    Andrée

    SMARTSHEET EXPERT CONSULTANT & PARTNER

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    W: www.workbold.com | E:[email protected] | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!