Smart Sheet Forms

Crisel Juarez

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,

Paul Newcome

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.