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
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 64.3K Get Help
- 423 Global Discussions
- 221 Industry Talk
- 462 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 144 Just for fun
- 59 Community Job Board
- 463 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 300 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!