Can I default cell values to a formula in the form without getting a ' in front?

SuzanneH

I have a form entry sheet where I have some columns hidden but defaulting to a formula. The goal is to have the columns populate with the results of the formulas without any updates required (like cutting and pasting the formula in the grid). It works but a ' is inserted so the cell does not calculate and requires someone to remove the '. Is there a way to eliminate the '?

Also, sometimes the new row is added about 6 rows down from my last row. How can I get it to always add to the next blank row?

Thanks!

Suzanne

Andrée Starå

Hi Suzanne,

To have the formula auto-filled, you need to have two or more rows above or below the formula submission with the formula and the same pattern. You can't and shouldn't add the formula to the form.

Why the form is added where there doesn't seem to be anything on the rows before is because there is something. It could be a color change or something else.

Can you describe your process in more detail and maybe share the sheet(s)/copies of the sheet(s) or some screenshots? (Delete/replace any confidential/sensitive information before sharing) That would make it easier to help. (share too, andree@getdone.se)

I hope that helps!

Have a fantastic week & Happy Holidays!

Best,

Andrée Starå

Workflow Consultant / CEO @ WORK BOLD

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

Andrée Starå

@SuzanneH

Glad we got it working!

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

Andrée Starå

@Katie Koons

Hi Katie,

You’d first need to delete the formulas in the form. Secondly, you’ll have to structure the sheet, so there are at least two rows with the same formula structure, and then the formula will auto-fill for each submission.

We can hide the formula rows with a filter to make the sheet look cleaner.

Would that work?

I hope that helps!

Be safe and have a fantastic week!

Best,

Andrée Starå

Workflow Consultant / CEO @ WORK BOLD

✅Did my post 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! 

Andrée Starå

Hi Suzanne,

To have the formula auto-filled, you need to have two or more rows above or below the formula submission with the formula and the same pattern. You can't and shouldn't add the formula to the form.

Why the form is added where there doesn't seem to be anything on the rows before is because there is something. It could be a color change or something else.

Can you describe your process in more detail and maybe share the sheet(s)/copies of the sheet(s) or some screenshots? (Delete/replace any confidential/sensitive information before sharing) That would make it easier to help. (share too, andree@getdone.se)

I hope that helps!

Have a fantastic week & Happy Holidays!

Best,

Andrée Starå

Workflow Consultant / CEO @ WORK BOLD

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

SuzanneH

Thanks Andree,

The blank rows seem to be the rows that get generated at the bottom of a smartsheet when you populate many rows (like drag and drop). Sometimes an additional few lines are added below... I think it is related to that? It sometimes doesn't have blanks before a new form line entry. It's random.

I am not sure I understand about having the formula on the above lines. If I do that, it inserts the new line with nothing in the formula columns. I need to cut and paste the formula in afterwards. I think the user is going to have an issue with that.

If the screenshots below are not enough, I can share sheet, let me know. i just used the form to add the Whole Foods line. This time no blanks were inserted. You can see the formulas as text.

Andrée Starå

Happy to help!

Here's more information about the Formula Autofill:

https://help.smartsheet.com/articles/1641473-auto-filling-formulas-and-formatting

I'd be happy to take a look if you want to share it with me.

Happy New Year!

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

SuzanneH

Thanks Andree, I'd like to share it with you so you can take a look. How do I do that? Your name is not an option when I click share.

Andrée Starå

Input my email address and it will work. Share to andree@getdone.se

SuzanneH

Ok great, I have shared it. Thank you.

Andrée Starå

Thanks!

I'll take a look and get back to you!

Andrée Starå

@SuzanneH

Glad we got it working!

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

Katie Koons

Andree,

I have the same problem! I am curious how you guys ended up resolving this issue? I am also using hidden fields in a form to "auto-populate" equations in two different columns. I am also getting the ' in front of the equation once the form is submitted. The form submissions (project requests) land at the top of the page and then our graphic designer drags the projects into different parent rows based on priority or project status. There are not always two or more rows above or below the form submission with the formula and the same pattern.

Any advice would be greatly appreciated!

Thanks,

Katie

Andrée Starå

@Katie Koons

Hi Katie,

You’d first need to delete the formulas in the form. Secondly, you’ll have to structure the sheet, so there are at least two rows with the same formula structure, and then the formula will auto-fill for each submission.

We can hide the formula rows with a filter to make the sheet look cleaner.

Would that work?

I hope that helps!

Be safe and have a fantastic week!

Best,

Andrée Starå

Workflow Consultant / CEO @ WORK BOLD

✅Did my post 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!