Formula to populate a cell from 1 of many populated columns
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
Best Answer

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)))))
Answers

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)))))

Thanks Mike... This is actually the direction I was going but I couldn't sort out the syntax
Works as expected
Thank you

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.

Thanks Guys

Happy to help! 👍️
Help Article Resources
Categories
Check out the Formula Handbook template!