Formula for always populating one cell if it's parent cell is populated with text

Options

Hi all,

Still trying to play with formulas. Below is a screen shot of my situation.

If there is text in the mRNA-#, CX-#, next to the RNA # MFG Dates, I want that same text to be in cell below highlighted. I want to template this MFG Info, I don't want to use the "=" it will be tedious to do every time. Can someone find a formula for this?


Will

Answers

  • Brian_Richardson
    Brian_Richardson Overachievers Alumni
    Options

    Hi Will - a bit more info please. Do you plan to always use the term "RNA # MFG Dates"? Or do you plan to always put the mRNA-#, CX-# next to the first subtask of MFG Info? Do you need the m-RNA-#, CX-# value repeated everywhere or just on that single next parent task? in other words what consistent place do you plan to put the info.

    BRIAN RICHARDSON | PMO TOOLS AND RESOURCES | HE|HIM

    SEATTLE WA, USA

    IRON MOUNTAIN

  • Will S.
    Will S. ✭✭
    Options

    Thanks Brian for the follow-up,

    I always plan to have a mRNA# or CX# next to the first subtask of MGF info. And yes, I need that same value repeated just next on that single next parent task.


    For follow-up Brian, as I expanded more on the sheet, this same ask is for the cells in green test


    For the CX portion, it would be localized to the RNA section (Input the CX, that populates the CX-XXX cell). From the LNP and DP section, I would want the mRNA-XX on the top line to populate the cells in green (I would input the mRNA-XX in the first cell next to XXXX).


    I hope this clarifies, and thank you for the follow up.

    Will

  • Will S.
    Will S. ✭✭
    Options

    HI there, wanted to circle back on this Brian, any help?

  • Brian_Richardson
    Brian_Richardson Overachievers Alumni
    Options

    Hi Will, sorry I was on break for a couple of weeks and lost track of this. I'll take a look. Anyone else out there with some ideas for WIll?

    BRIAN RICHARDSON | PMO TOOLS AND RESOURCES | HE|HIM

    SEATTLE WA, USA

    IRON MOUNTAIN

  • Christian Graf
    Christian Graf ✭✭✭✭✭
    edited 01/06/23
    Options

    Hello @Will S.,

    Does it work to just place the formula =[mRNA-#, CX-#]4 for the cell with (populate from CX-XXXX)?

    Similarly you can place =[mRNA-#, CX-#]1 for the other sections?

    Or are you looking for a formula you can drag down? Are your tasks the same or do they change?

    If you want a formula you can drag down the whole column, you could do something like:

    =IF(AND(PARENT([Program/Tasks]@row) = "RNA", [Program/Tasks]@row = "Reference Material"), $[mRNA-#, CX-#]$4, IF(OR([Program/Tasks]@row = "Reference Material", CONTAINS("MFG Dates", [Program/Tasks]@row)), $[mRNA-#, CX-#]$1))

    Hope this helps!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!