Convert to Column Formula

jgneely72151
jgneely72151 ✭✭✭✭✭
edited 01/20/22 in Formulas and Functions

Is there a work around for "Convert to Column Formula" in the Primary Column that has an hierarchy?

We love the hierarchy groupings and I do have a formula there that gives a sneak peak of the name and site assigned at first glance but it is cumbersome to go to the sheet to copy the formula down. I was hoping it could be automatic as I have a lot staff who are not comfortable with making this update. Are there any other options to make this easier for me so that my colleagues readily see this information?

Here's an example of what my sheet looks like and this is the formula used: =Last2 + ", " + First2 + "-" + Site2

Any help is greatly appreciated!


Tags:

Best Answer

  • Genevieve P.
    Genevieve P. Employee Admin
    Answer ✓

    Hi @jgneely72151

    The issue here is that you want the formula to apply only to Children rows, but you need to be able to manually type a value into the Parent row. Because of this, you can't apply a column formula because this would apply to every single cell.

    You could potentially make sure that you enter "WEST" or "EAST" into the First column as well; that would then bring in this value to the Primary Column and you could adjust your formula to use @row instead of the row number, making it applicable to the entire column:

     =Last@row + ", " + First@row + "-" + Site@row

    See: Create Efficient Formulas with @cell and @row

    Cheers,

    Genevieve

Answers

  • Genevieve P.
    Genevieve P. Employee Admin
    Answer ✓

    Hi @jgneely72151

    The issue here is that you want the formula to apply only to Children rows, but you need to be able to manually type a value into the Parent row. Because of this, you can't apply a column formula because this would apply to every single cell.

    You could potentially make sure that you enter "WEST" or "EAST" into the First column as well; that would then bring in this value to the Primary Column and you could adjust your formula to use @row instead of the row number, making it applicable to the entire column:

     =Last@row + ", " + First@row + "-" + Site@row

    See: Create Efficient Formulas with @cell and @row

    Cheers,

    Genevieve

  • jgneely72151
    jgneely72151 ✭✭✭✭✭

    The formula definitely worked but not quite like what I was looking for. I'll make it work though. Thank you so much!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!