Formula to populate a cell from 1 of many populated columns

Detrie Zacharias

I have 5 columns.

Only 1 of the 5 will ever be populated per row 

I need help with a formula to populate a cell with the value of the column that has a value. 

I tried =([Cell1]@row+[Cell2]@row+[Cell3]@row+[Cell4]@row+[Cell5]@row)

It seems to work but it gives me a leading zero

Mike Wilday

This formula is bulky but it would do the trick as long as only one cell would contain data. 

=IF(NOT(ISBLANK([cell1]@row)), [cell1]@row, IF(NOT(ISBLANK([cell2]@row)), [cell2]@row, IF(NOT(ISBLANK([cell3]@row)), [cell3]@row, IF(NOT(ISBLANK([cell4]@row)), [cell4]@row, IF(NOT(ISBLANK([cell5]@row)), [cell5]@row)))))

Mike Wilday

This formula is bulky but it would do the trick as long as only one cell would contain data. 

=IF(NOT(ISBLANK([cell1]@row)), [cell1]@row, IF(NOT(ISBLANK([cell2]@row)), [cell2]@row, IF(NOT(ISBLANK([cell3]@row)), [cell3]@row, IF(NOT(ISBLANK([cell4]@row)), [cell4]@row, IF(NOT(ISBLANK([cell5]@row)), [cell5]@row)))))

Detrie Zacharias

Thanks Mike... This is actually the direction I was going but I couldn't sort out the syntax

Works as expected

Thank you

Paul Newcome

For future reference, another option would be a JOIN/COLLECT combo...

 

=JOIN(COLLECT([cell 1]@row:[cell 5]@row, [cell 1]@row:[cell 5]@row, NOT(ISBLANK(@cell))))

.

NOTE: This will only work if the columns are next to each other with no interruption in the range. Otherwise Mike's solution would be the easiest way to go.

Detrie Zacharias

Thanks Guys

Paul Newcome

Happy to help! 👍️