populating specific cells in new sheet by combining forms and templates

Adi Lavy · 03/05/21

Hi

I would like to create a template that could be personalized the users once they choose to use the template.

For example:

I have a template that creates a project implementation tracking sheet.

I would like the user to be able to select the template and then he will be presented with a form asking what is the name of the project , what is the hours estimate, and who is leading the project.

This data will then be populated in the new sheet he just created from the template, but it should be inserted into specific cells (not just columns).

Is there such a feature in Smartsheet?

Thanks!

Andrée Starå · 03/08/21

@Adi Lavy

Ok.

If you don't need the intake process for something else, I recommend adding the information to the Template sheet instead. You can have a section at the top or use the Sheet Summary section to input all the information as needed.

If you want to simplify the look for the ones adding the information, you could use a so-called helper sheet to input everything, and it would be part of the Template Kit and connected to the Project sheet.

Would that help?

Andrée Starå · 03/06/21

Hi @Adi Lavy

I hope you're well and safe!

There are a few options.

Intake process with a form and then use the save as a new folder/workspace and then fill in a sheet or a section of the sheet.
Template with a section or a form
Premium add-on, Control Center which almost automates everything

What do you think?

Would any of those options work/help?

I hope that helps!

Be safe and have a fantastic weekend!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please help the Community by marking it as the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!

Adi Lavy · 03/08/21

Hi Andrée

Thank you for your reply!

An intake form might work, but can I direct the input of the intake form to populate specific fields (ex. col1 on row 5) in the newly created sheet?

Thank you again

Adi

Andrée Starå · 03/08/21

@Adi Lavy

Happy to help!

Yes.

You'd connect the sheet(s) with cross-sheet formulas and VLOOKUP or INDEX/MATCH, and then you can input a project id, and the sheet will collect the information from the intake sheet.

Make sense?

Adi Lavy · 03/08/21

I think so. Ideally I would like to have the name of the project also entered in the intake form, so I can't use it for the VLOOKUP. So I guess I'll need some other identifier. I wonder if I can do something like this:

Have the user enter the name of the project in the intake form
The name of the project will then be used to automatically name the new folder and sheets
Some fields in the new sheet will use the name of the sheet (or part of it), for example to specifically hold the name of the project in the sheet
Have a VLOOKUP function to search and pull the rest of the data from the intake form sheet.

Andrée Starå · 03/08/21

@Adi Lavy

Excellent!

You can use the Project Name, but it has to be something unique.

The automation part isn't available natively, so you'd need to use the Smartsheet API or similar. If it's an option, I would recommend using the Premium add-on, Control Center.

Is that an option, or do you have another plan for the automation?

Let me know if I can help in any way!

Adi Lavy · 03/08/21

I understand what you mean about the API and an add-on. Currently I am only evaluating Smartsheet's capabilities so it's good to know that this is possible. I need to assess if following this path is going to take too much of my time vs. just asking the user to type this information into the sheet after a template is deployed.

Thank you for all your help!

Andrée Starå · 03/08/21

@Adi Lavy

You're more than welcome!

How many projects are we talking about monthly/yearly?

✅Remember! Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it as the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!

Adi Lavy · 03/08/21

~tens of projects each year

Andrée Starå · 03/08/21

@Adi Lavy

Ok.

If you don't need the intake process for something else, I recommend adding the information to the Template sheet instead. You can have a section at the top or use the Sheet Summary section to input all the information as needed.

If you want to simplify the look for the ones adding the information, you could use a so-called helper sheet to input everything, and it would be part of the Template Kit and connected to the Project sheet.

Would that help?

Adi Lavy · 03/08/21

I think it would. I will look for more information about how to use use a Sheet Summary to enter data and then pull the information from the summary to the main project sheet.

Thank you @Andrée Starå !