Dynamically fill a cell with text
I have included a picture to help tell the scenario, but I'm trying to dynamically populate a cell with the question's name with the highest result. The actual form I'm using has over 160 columns, so I'm trying to find a way to automate the top result(s) with the question's name. The expectation is to eventually scale this to list the top 5 to 10 questions.
Thanks for any help you all can provide!
Best Answers
-
The basic idea would be:
=INDEX(COLLECT([Column2]1:[Column5]1, [Column2]@row:[Column5]@row, @cell = MAX([Column2]@row:[Column5]@row)), 1, 1)
what do you want to do when there is 2 equal ones?
You could join them:
=JOIN(COLLECT([Column2]1:[Column5]1, [Column2]@row:[Column5]@row, @cell = MAX([Column2]@row:[Column5]@row)), ", ")
If you want this to be a column formula you would do as below:
=JOIN(COLLECT(INDEX([Column2]:[Column5], 1), [Column2]@row:[Column5]@row, @cell = MAX([Column2]@row:[Column5]@row)), ", ")
-
Thanks, @Leibel Shuchat! These formulas gave me the results I needed. I appreciate it very much!
Answers
-
The basic idea would be:
=INDEX(COLLECT([Column2]1:[Column5]1, [Column2]@row:[Column5]@row, @cell = MAX([Column2]@row:[Column5]@row)), 1, 1)
what do you want to do when there is 2 equal ones?
You could join them:
=JOIN(COLLECT([Column2]1:[Column5]1, [Column2]@row:[Column5]@row, @cell = MAX([Column2]@row:[Column5]@row)), ", ")
If you want this to be a column formula you would do as below:
=JOIN(COLLECT(INDEX([Column2]:[Column5], 1), [Column2]@row:[Column5]@row, @cell = MAX([Column2]@row:[Column5]@row)), ", ")
-
Thanks, @Leibel Shuchat! These formulas gave me the results I needed. I appreciate it very much!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.9K Get Help
- 439 Global Discussions
- 138 Industry Talk
- 471 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 68 Community Job Board
- 486 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!