Trying to create a formulated/formatted sheet from the inputs of online Form

Aydin Birik

Hello. I am stuck here.

I created a form. In the sheet file, I have some formatting and formulas to run reports based on the inputs coming from the online form.

However, when a new input comes into the sheet as a new row, my formats and formulas that I set up in upper rows do not apply to this newly created one!. I have to manually go into the sheet file and apply formats and formulas from the upper row to this newly created row all the time.

I tried creating a bunch of empty rows with the formulas and formats already set in place, and expect new inputs will be placed into those cells, but in this case, those empty rows get treated as new data inputs and get cell data of "created date" and "created by", gives #Invaliddata type error where formulas and formatting is applied, messes my overall sheet data,- and even worse the new inputs coming from the form position right under to the end of those empty rows - which I end up with where I started at.

So frustrating.

Can you pls help me?

Mark Cronk

Hi Aydin,

Delete all of the rows below your last row of data. Save your sheet. Apply the desired formats to your columns. When new rows are added to the bottom they should adopt the format of the rows above and column.

I've found that formatting is a little more consistent if you can add new rows to the top of your sheet. Is that an option for you?

Work?

Mark

Andrée Starå

Hi @Aydin Birik

I hope you're well and safe!

To add to Mark's excellent advice/answer.

I'd recommend, if possible, using column formulas instead and Conditional Formatting rules.

More information about the Formula and Formatting autofill.

The new row will be auto-filled with the formulas if there are two rows below/above with the same formulas and the same hierarchy level.

More details:

If the formula structure is the same above/below, the Formula Autofill will automatically add the formula(s).

Conditions That Trigger Formula Autofill

You’ll see formulas populate automatically when you type in a newly inserted or blank row that is:

• Directly between two others that contain the same formula in adjacent cells.
• At the topmost of the sheet if it’s above two rows that have the same formula in adjacent cells. (This includes rows inserted from a form.)
• At the bottommost of the sheet if it’s below two rows that have the same formula in adjacent cells. (This includes rows inserted from a form.)
• Above or below a single row that is between blank rows and has formulas.

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

Would that work/help?

I hope that helps!

Be safe and have a fantastic week!

Best,

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

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

Mark Cronk

Yes, under form settings there is an option to have new data appear on the top.

https://help.smartsheet.com/articles/2481836-manage-form-display-and-submission-options

Get the result you need?

Mark

Mark Cronk

Hi Aydin,

Delete all of the rows below your last row of data. Save your sheet. Apply the desired formats to your columns. When new rows are added to the bottom they should adopt the format of the rows above and column.

I've found that formatting is a little more consistent if you can add new rows to the top of your sheet. Is that an option for you?

Work?

Mark

Andrée Starå

Hi @Aydin Birik

I hope you're well and safe!

To add to Mark's excellent advice/answer.

I'd recommend, if possible, using column formulas instead and Conditional Formatting rules.

More information about the Formula and Formatting autofill.

The new row will be auto-filled with the formulas if there are two rows below/above with the same formulas and the same hierarchy level.

More details:

If the formula structure is the same above/below, the Formula Autofill will automatically add the formula(s).

Conditions That Trigger Formula Autofill

You’ll see formulas populate automatically when you type in a newly inserted or blank row that is:

• Directly between two others that contain the same formula in adjacent cells.
• At the topmost of the sheet if it’s above two rows that have the same formula in adjacent cells. (This includes rows inserted from a form.)
• At the bottommost of the sheet if it’s below two rows that have the same formula in adjacent cells. (This includes rows inserted from a form.)
• Above or below a single row that is between blank rows and has formulas.

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

Would that work/help?

I hope that helps!

Be safe and have a fantastic week!

Best,

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

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

Aydin Birik

https://community.smartsheet.com/discussion/comment/292569#Comment_292569

Thanks for the reply.

I can try deleting all of the empty rows below my last row of data, apply desired formats (which are already set up in the data cells) and expect to have same format/formulas to be applied to the newly created rows created by input data from the Form then.

(The data is coming from a Form, so all the form data coming from there gets position to the bottom of the sheet. There is no option to use top of my sheet)

Mark Cronk

Yes, under form settings there is an option to have new data appear on the top.

https://help.smartsheet.com/articles/2481836-manage-form-display-and-submission-options

Get the result you need?

Mark