Reference column at level zero and ancestors to pull in data to new columns

I want to pull series and item from the series column into their own columns

The series is part of the item number as well: B060 (series), item in series is B060-122 (item)

In a new column titled Series ID I want the series number on every row level 0 and level 1

In a new column titled Item ID I only want the item # on the level 1 rows and on level 0 it should have NA as the result

I have provided image of what I am trying to achieve

Best Answer


  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    It looks like we may not necessarily be able to use levels or indentation as a starting point.

    B060 is on level zero and has B060 in the first column and NA in the second column, but A3000316 is on the same level and has NA in the first column and A3000316 in the second column. Both entries are on the same level, but they are being treated differently.

  • PDunn
    PDunn ✭✭✭✭✭

    Can I utilize the category and subcategory columns so if category is Dining = series level 0, If accessories @ level 0 , which have no child rows. I'll try to create something and if I need additional assistance I'll reach back out to you. Thanks Paul

  • PDunn
    PDunn ✭✭✭✭✭

    @Paul Newcome I wanted to follow up with my final solution that worked perfectly.

    The above formula places the series # into the Series ID column

    The above formula places a zero in Item ID column if a parent or a single level 0 row. Then if a it is a child row it places item # into Item ID column.

  • PDunn
    PDunn ✭✭✭✭✭

    @Paul Newcome I am back for additional assistance.

    I now need to have a zero for a specific category in the series column. I have these two separate formulas that work independently how can I combine them so it brings back series # for everything except the category of home accents.

    =IF(Level@row >= "1", INDEX(ANCESTORS(Series@row), 1), Series@row) - places the parent series in each row

    IF(AND(Level@row >= "0", Category@row = "Home Accents"), 0) - places a zero in Series ID if category = Home Accents

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓

    You would drop the first one into the third portion of the second one to create a nested IF.

    =IF(AND(...............), 0, IF(Level@row = ....................))

  • PDunn
    PDunn ✭✭✭✭✭

    @Paul Newcome success thanks so much for the simple way you explain complex formulas.

    Final formulas that work for both columns

    Series ID:

    =IF(AND(Level@row = "0", Category@row = "Home Accents"), 0, IF(Level@row = "1", INDEX(ANCESTORS(Series@row), 1), Series@row))


    Item ID:

    =IF(AND(Level@row = "0", Category@row = "Home Accents"), Series@row, IF(Level@row = "1", Series@row, IF(Level@row = "0", 0)))

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!