Smart Sheet Forms
Hello there,
I need to capture information for a weekly status update, I am planning to use Smart Sheet forms to gather the status updates, and then consolidate those multiple rows into one single row. The challenge I am facing is that for consolidating all of the data in one single row I use the INDEX() MATCH() formula, this formula works well if we have one entry (row) per user, however, when I have a user that have multiple entries since we have the same name, the formula does not work because I need a key to differentiate.
another way that I tried to solve this issue was to create two forms for one sheet, but how do I add a button where when the first entry is captured, the user answers the question if he has another project to update and if they answer yes, then opens a the second form that will capture the project in a different line.
Thanks,
Answers
-
You could have the form entries populate at the top of the sheet.
If you want them to continue populating at the bottom of the sheet, you are going to want to insert a system generated Created (date) type column, a text/number column, and a checkbox column. In the text/number column you can use
=VALUE(YEAR(DATEONLY(Created@row)) + "" + IF(MONTH(DATEONLY(Created@row))< 10, "0") + MONTH(DATEONLY(Created@row)) + "" + IF(DAY(DATEONLY(Created@row))< 10, "0") + DAY(DATEONLY(Created@row)))
In the Checkbox column:
=IF([Text/Number]@row = MAX(COLLECT([Text/Number]:[Text/Number], [User Name]:[User Name], [User Name]@row)), 1)
Then to pull the most recent entry, you would use
=INDEX(COLLECT({Range to pull from}, {User Name Column}, "John Smith", {Checkbox Column}, 1), 1)
To get the form to reload, you can go into the form settings and have it set so that the form automatically reloads for a new submission once an entry has been made.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.1K Get Help
- 348 Global Discussions
- 199 Industry Talk
- 427 Announcements
- 4.4K Ideas & Feature Requests
- 133 Brandfolder
- 127 Just for fun
- 127 Community Job Board
- 455 Show & Tell
- 28 Member Spotlight
- 1 SmartStories
- 282 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!