locking row to the top

ross chambers · 02/09/23

I have a sheet that gets filled with data from a form, is there a way I can lock the top row so when the form is filled the new row appears below the top row. I have the row locked but new rows from the form always appear above it. Just having the row appear at the bottom wont work for me as I want formulas in the top locked row to automatically fill in the new rows from the sheet. Having a column formula wont work either as I want the user to be able to edit the data at any time and column formulas wont allow that.

Andrée Starå · 02/09/23

Hi @ross chambers

I hope you're well and safe!

You'd have to add at least add one more row at the top with the same formula structure, and then the submissions would auto-populate with the same. (You can set a default filter to hide the so-called helper rows)

Make sense?

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, Awesome, or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

ross chambers · 02/09/23

Hi @Andrée Starå

That doesn't work for me, even if I have 2 rows that are locked at the top, new rows from the form still appear above it so if anyone manually enters data it will overwrite the formula and then when the next row is added it wont have all the formulas because one of the cells no longer has it so there not 2 rows with the formula below it anymore. Is there anyway the form can populate row 3 instead of row 1

Andrée Starå · 02/09/23

@ross chambers

I forgot to include that you'd need to change the setting below in the form settings.

Did that work/help?

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

ross chambers · 02/09/23

@Andrée Starå that still doesn't help as if they're added to the bottom and a cell is manually changed the formula will be gone and then the next row added below that wont have all the formulas as both cells above wont have the formula. That's why I want to lock the 2 top rows in place so they'll never be changed and every new row added will autofill the formula, do you get what I'm trying to do?

ross chambers · 02/09/23

I basically want column formulas but allow the cells to be changed manually

Andrée Starå · 02/09/23

@ross chambers

Yes, I get it now.

I'd recommend adding a so-called helper sheet where the form populates and then have it copy/move over to the main sheet.

Make sense?

Would that work/help?

Andrée Starå · 02/09/23

https://community.smartsheet.com/discussion/comment/361384#Comment_361384

@ross chambers

Yes, that would be a fantastic feature.

Please submit this as a Product Feedback or Idea (If it hasn't been added already) when you have a moment.

Another method is to add so-called helper columns instead.

One with the column formula and another to override the value, and a third for the value to use.

ross chambers · 02/09/23

@Andrée Starå That's what I'm doing now, I have a form that populates the first few columns and then based on that info someone fills in a form on another sheet which is then brought over to the original sheet using formulas. That is the main way people should be inputting data and that would be fine to use column formulas but if they make a mistake they'd have to use the form again and reenter all the data so would be easier for them if they could just change the cell but thats not possible with column formulas so im trying to find a work around using autofill of forumlas

Andrée Starå · 02/09/23

@ross chambers

That's not the same method (If I'm not misunderstanding)

My method copies or moves the row to the main sheet, and then they can edit as needed.

And the formula autofill or column formula in the form sheet would autofill.

Make sense?

ross chambers · 02/09/23

@Andrée Starå

If I just copied or moved the row over onto the main sheet it wouldn't join the original row it would just make a new one so the original row wont be updated

Andrée Starå · 02/09/23

@ross chambers

Aha! I misunderstood and didn't catch that you're joining the information into one row.

ross chambers · 02/09/23

@Andrée Starå so do you have any idea how I could Workaround that?

Paul McGuinness · 02/10/23

Hi @ross chambers

You could still utilise the row copy feature @Andrée Starå referenced, see if this would work for you.

Master sheet with column formulas receives completed form submission
Column formulas run in the master sheet and populate the rest of the row data based on the three completed columns received from the webform.
That full row of data is then automatically moved to a second sheet that receives just the data and has no formulas running in it. Moving rows that contain column formulas to a sheet with no formulas adds only the data.
Trigger to move could be once a day or if the users may need to edit errors within a short timeframe then the trigger could be once the auto fields have been automatically populated etc.
Users are free to amend the data in that second sheet as it is data only (you could lock out certain columns if required, but that may not be needed)

So you retain the form usage, column formulas on the master sheet and the users are able to fix any errors in the second sheet.

Hope that helps

Thanks

Paul

ross chambers · 02/13/23

Hi @Paul McGuinness

Thanks but I'm not sure that would work for me since its a bit more complicated than that.

The first "Request Form" populates the first five columns of the main sheet, this data is then shown on a dashboard with the second "Estimation Form" embedded into it. Someone then completes the estimations and fills out the "Estimation Form" which saves the data to a second sheet which is then brought over to the main sheet using formulas. This data is then shown on another dashboard with a third "Decision Form" Someone else then fills in the form and changes the final decision column to one of 3 choices and depending on the choice the row is then moved to the correct sheet. So all the information inputted on the 3 forms need to be combined into one row and then moved to the correct sheet with automation. this all works great with column formulas but you have to go and redo the form if a mistake is made. If there's a way to do this with the method you're suggesting I'll try just can't wrap my head around doing that way

Paul McGuinness · 02/13/23

Hi @ross chambers

Thanks for the clarity around the needs, looking at the info provided it should still work but will require a couple more sheets to act as data tables (sheet 2 and 4) to allow the webform data to be pulled backwards into the preceding sheet.

If i were building it, below is a mock up of how i would approach it, once you get the structure imagined its actually fairly straightforward.

Hope that helps

Thanks

Paul

locking row to the top

Answers

Categories