Can I default cell values to a formula in the form without getting a ' in front?
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
Best Answers
-
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!
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
-
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!
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
-
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!
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
Answers
-
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!
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
-
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.
-
Happy to help!
Here's more information about the Formula Autofill:
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!
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
-
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.
-
Input my email address and it will work. Share to andree@getdone.se
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
-
Ok great, I have shared it. Thank you.
-
Thanks!
I'll take a look and get back to you!
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
-
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!
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
-
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
-
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!
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63K Get Help
- 379 Global Discussions
- 212 Industry Talk
- 442 Announcements
- 4.6K Ideas & Feature Requests
- 140 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 304 Events
- 34 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!