Using a form to submit hours for projects

Bridget Sloane

I would like to set up a simple sheet where PM can list several projects and anticipated # of hours to complete each project. I would then like a form or some type of automation that allows employees who are working on a project to submit the # of hours they have worked on it. Is this possible to do with a form and if so, how would I set up the form so that the submitted hours were attached to the correct project?

jmyzk_cloudsmart_jp

i @Bridget Sloane

The demo solution I've outlined below uses the following method (*) to automatically fill the "Project ID" and "Name" in the "of hours worked on" sheet's form based on the "Row ID" and "Project" values from the "list of projects and anticipated # of hours" sheet. (To "set up the form so that the submitted hours were attached to the correct project")

I've placed the form URL in the Sheet Summary field of the sheet, as illustrated in the image below. (This can be referenced as FormURL#):

=FormURL# + SUBSTITUTE("?ID=" + [Row ID]@row + "&Project=" + Project@row, " ", "%20")

This URL prefills the form, which then inputs data into a separate sheet titled "# of hours worked on."

For example, if you click the URL in the fourth row, the URL prefills the ID with 4 and Project 4, as shown below.

I utilized this URL query string technique since Smartsheet does not currently support dynamically changing a dropdown list based on data from another sheet. The Smartsheet Learning Center has more details on prefilling form fields using URL query strings.

(*) https://help.smartsheet.com/articles/2478871-url-query-string-form-default-values

After the data for "# of hours worked on" is entered into the sheet, we can use the SUMIFS function to calculate the total "# of hours worked on so far" as follows:

=SUMIFS({# of hours worked on Range : hours}, {# of hours worked on Range : ID}, [Row ID]@row)

Testing the Solution:

The provided link allows you to explore this solution firsthand and test how it works by entering hours worked on a project in the form (Smartsheet login is required, but the "Created By" column is hidden for privacy).

https://app.smartsheet.com/b/publish?EQBCT=7ffe39fb49394ae3ae4e3b17736c0490