10 Blank Rows at the Bottom of Sheets
From what I understand, Smartsheet always adds 10 blank rows to the bottom of the sheet, and there is nothing that can be done to stop it. My problem is I have a master project schedule with numerous "helper" columns with formulas that need to be in every row that has a project associated with it. The blank rows do not autofill the formulas until a project is entered, but only if the project is entered on the next available blank row. I have attached two examples to explain what I am describing. Example 1 shows a new project entered on the next available line - Everything works fine. Example 2 shows what happens if someone enters a project in any of the other blank rows - The formulas do not autofill, and there are tons of problems.
Example 1:
Example 2:
There are many people entering projects into this sheet, and most of them do not understand what is going on behind the scenes in the sheet. I'm hoping someone can offer a solution of how to stop example 2 from happening. I even tried locking the blank rows to force everyone to insert new rows to create projects, but Smartsheet just creates 10 more blank rows.
The only other solution I can think of is to create a ton of rows and drag the formula down, but at some point in the future, I would run into the same problem again as the rows fill up, and I'd rather not have 1,000 unnecessary rows in my sheet.
I suppose using a form to create a project might work, but I'm unsure what further problems that would cause with formulas autofilling, and I'd prefer not to introduce a new workflow at this point.
Thanks in advance!
Best Answer
-
Ah. Cell linking is why I don't use forms on some of mine as well. One of the data points captured per row is their email address, so I use the bottom row to say "Insert Above Here", and (because of cell linking) only one row gets inserted at a time anyway. I also have an alert that goes to the user (similar to you) if the formulas don't autofill into a new row as a reminder that they need to follow instructions, and that same alert also goes to me so that I can go ahead and fix it.
Answers
-
Forms would not affect the auto-fill. In fact it would ensure that each new row is put in directly under the currently used ones and is a very good way of ensuring autofill.
Another option would be to use the very last row as an instructional/formatting row. Use the last row to tell people to insert new rows above that one. That will make sure that the rows immediately above their newly entered row will meet the autofill conditions.
-
Thanks, Paul. I will look into using a form. I've just seen other posts of people complaining of autofill not working properly with forms either. If nothing else, I will use the last row for instructions as you suggested.
I ran into another issue while exploring this: If someone inserts multiple rows before entering anything in those rows, the autofill only applies to the first row that was inserted. This is also a major problem. Any ideas for that one? It seems like there should be an easier way to ensure that all rows will have the appropriate formulas...
-
The main cause for autofill issues with forms is that people either do not fill in the first two rows as "formatting rows" or they do not delete the rows they used for testing.
To use a form, the last to used rows in the sheet must contain the formulas. The big difference here is going to be used vs blank.
If you enter data into row 1 and then delete the data from the cells, row 1 is still considered used even though it is blank. Since it has been used, the form will populate on row 2 even though row 1 is blank. To correct this, you would need to delete the row itself and not just the test data. That is typically why people have issues with forms populating on the wrong row which would in turn not meet the autofill requirements.
Forms are GREAT for making sure new data is entered one row at a time either at the top row or the last row of the sheet. You just have to make sure it is set up correctly is all.
-
Thanks again, Paul.
For our particular situation, a form does not really help because we rely on linking cells to projects sheets for due dates in this master schedule, so everyone would still need access to edit this sheet. Thus there would be no way to stop them from simply inserting rows to the sheet instead of going through the extra step to fill out the form.
I've decided to go with a different option that I believe to be the lesser of four evils. I have simply dragged the formulas down for several hundred rows, and added a column with a formula that simply checks a box. I then created an automated workflow that will alert me if there is something in the "Project Name" column and the box is not checked (formulas didn't autofill).
This isn't perfect, but it takes care of 95% of the problem and alerts me when the other 5% occurs.
-
Ah. Cell linking is why I don't use forms on some of mine as well. One of the data points captured per row is their email address, so I use the bottom row to say "Insert Above Here", and (because of cell linking) only one row gets inserted at a time anyway. I also have an alert that goes to the user (similar to you) if the formulas don't autofill into a new row as a reminder that they need to follow instructions, and that same alert also goes to me so that I can go ahead and fix it.
