Skip to main content
Solved

Nested Formula Help

  • August 30, 2020
  • 2 replies
  • 16 views
J

This is probably so easy, but I just can’t get it to work.

I hav 13 columns that are using a formula to extrapolate a comma separated list from a single column. That works great.

I am then trying to use a new formula field to look across all 13 and put the result of the last column that is not blank.

(i.e. if column 1 has text then check column 2. If column 2 is blank then column 1 in field if column 2 has text then check column 3 and if 3 is blank them column 2 and so on.

the columns are called item1, item2, item3 and so on.

Thanks in advance

Best answer by kuovonne

Instead of starting from the beginning of the list, try a formula that starts at the end of the list.

IF(item13, item13,
IF(item12, item12,
IF(item11, item11,
IF(item10, item10,
IF(item9, item9,
IF(item8, item8,
IF(item7, item7,
IF(item6, item6,
IF(item5, item5,
IF(item4, item4,
IF(item3, item3,
IF(item2, item2,
IF(item1, item1
)))))))))))))

2 replies

K
  • Kris11
  • Inspiring
  • August 31, 2020

Hi Jason,

Interesting challenge. I’m operating on the logic that each successive item can only be filled if the one before is filled first, (ie. item3 has to be filled before item4 is filled.)

In that case, I think this is what you need. (Totally untested).

IF({Item1},IF({Item2},IF({Item3},IF({Item4},IF({Item5},IF({Item6},IF({Item7},IF({Item8},IF({Item9},IF({Item10},IF({Item11},IF({Item12},IF({Item13},"ALL ITEMS FILLED",{Item12}),{Item11}),{Item10}),{Item9}),{Item8}),{Item7}),{Item6}),{Item5}),{Item4}),{Item3}),{Item2}),{Item1}),"NO ITEMS")

It’s a messy one I’m not sure how to make pretty. But here is what’s being combined:

IF({Item1},notblank,"NO ITEMS")
IF({Item2},notblank,{Item1})
IF({Item3},notblank,{Item2})
IF({Item4},notblank,{Item3})
IF({Item5},notblank,{Item4})
IF({Item6},notblank,{Item5})
IF({Item7},notblank,{Item6})
IF({Item8},notblank,{Item7})
IF({Item9},notblank,{Item8})
IF({Item10},notblank,{Item9})
IF({Item11},notblank,{Item10})
IF({Item12},notblank,{Item11})
IF({Item13},"ALL ITEMS FILLED",{Item12})

In each line, looking if an item is blank, and if it is then it resolves the text from the previous item. If it’s ‘notblank’ then continues to the next IF statement. If the first item is empty, it should say “NO ITEMS” and if all the items are filled out, it should say “ALL ITEMS FILLED”

Again, totally untested, but hopefully helpful :slightly_smiling_face:

kuovonne
Forum|alt.badge.img+29
  • kuovonne
  • Brainy
  • Answer
  • August 31, 2020

Instead of starting from the beginning of the list, try a formula that starts at the end of the list.

IF(item13, item13,
IF(item12, item12,
IF(item11, item11,
IF(item10, item10,
IF(item9, item9,
IF(item8, item8,
IF(item7, item7,
IF(item6, item6,
IF(item5, item5,
IF(item4, item4,
IF(item3, item3,
IF(item2, item2,
IF(item1, item1
)))))))))))))
J
Terms & ConditionsAccessibility statement