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
- Customer Resources
- 64.9K Get Help
- 441 Global Discussions
- 139 Industry Talk
- 471 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 68 Community Job Board
- 496 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!