Feature Request: Static formula's for a column

Rachel Ambler

My Sheets are heavly Forumula laiden and this, in itself creates an issue insofar as each time I create new rows I have to ensure all my formulas are copied from a blank row.

 

I'd love to be able to define a formula inside a column that means that the formula is ALWAYS used whenever a new row is inserted etc.

Travis

 

Rachel - Our Auto-filling Formulas function will auto-fill a formula when a new row is added if two rows above or below contain identical formulas. Here is information on this functionality: http://help.smartsheet.com/customer/portal/articles/1641473-auto-filling-formulas-and-formatting

David Bright

Hi, I have exactly the same requirement and am having lots of trouble trying to get this to work consistently using the "Auto-Fill" feature. It simply does not always work.

 

This is a big issue for me right now... I have over 200 people using more than 200 sheets and reports that I need this functionaly for...

 

Is there any way that you can build a function to associate an formula with a column? Maybe apply that to the column header?

 

Thanks very much.

David

Travis

David, When rows meet the auto-fill criteria, it should always work (when new rows are added and where an identical formula is located in the two rows directly above or below the newly added row). Could you provide some steps for when the auto-fill formula does not work for you?

David Bright

Hi Travis,

Sure... I have a number of columns with formulas, and the experience is inconsistent overall.

For sheets with existing columns, if a user starts typing on a new row at the bottom of the existing rows, in some cases the formulas are auto-filled, but in other cases they are not auto-filled. I.e. they are not "Inserting" a new row... just typing on the "blank" rows at the bottom of the list.

Another situation is if the user imports, or copies a new row into the sheet from another sheet, then for some reason, in some cases, there are a bunch of blank rows between the bottom of the existing list and the newly imported, or pasted row.

This also happens when using a Web Form to capture new rows into an existing sheet. (I figured out a work-around for that situation by forcing the Web Form to insert the new row at the top of the sheet, but that's not always ideal).

 

The bottom line is that the Auto-fill feature does not always work consistently for me.

Regards,

David

 

 

 

Travis

Thanks David, I believe I understand the cause of all of these issues! There is a difference between a new row and a blank row. A new row is a row that has never had any data added to it (no cell history, text, formatting, etc). A blank row is a row that had data at one point, but the data was deleted from the row. They both could look the same in your sheet but some functionality will be different between the two. 

 

Auto-filling: this is only applied to new rows. Adding text to a blank row will not cause the formula to auto-fill.

 

Moving rows to a sheet / new web form entries: these are added to the next new row in your sheet. It will skip over any existing rows (even blank rows) and will add the data to the next new row.

 

To fix this: Delete the blank rows in your sheet. You can do this in bulk by single left clicking on the first blank row number, hold shift, and single left click on the last blank row number. Right click in the highlighted area and select Delete Row.

 

To prevent this in the future: rather than deleting the contents of a row, delete the entire row by right clicking on the row number and selecting Delete Row. 

David Bright

Thanks Travis,

I appreciate all the feedback and guidance, but unfortunately this does not fix the fundamental problem...

 

I am currently doing what you are suggesting... I go in and delete everything below the last row in a sheet... then it works for a while, but for various reasons that I'm not necessarily aware of, over time blank rows appear that the bottom of these sheets.

It may be because of user input, but I'm not sure.

 

As I mentioned in the beginning, I have over 200 sheets, and over 200 users, so it's not really feasible for me to open each sheet every day and delete all the blank rows...

 

So I'd like to ask my question again - is it not possible for you guys to implement a new feature in Smartsheet that allows a sheet Owner or Admin to define a formula at the "column header" level? And then have that formula apply to all cells in that column, irrespective of if it is a "new" row, or a "blank" row... I.e. the formula is always there and can never be deleted...

 

Thanks again for all your support, ideas, feedback, and guidance, it is greatly appreciated. I love Smarstheet, and we are basically using it now to run our business, so it's super critical for us.

 

Thanks,

David

Travis

Hi David, I will submit your suggestion to our product team as an enhancement request and it will be considered for future development. 

 

When you open a sheet, there are always rows added to the bottom of the sheet. These are new rows and they do not need to be deleted. The only rows that would need to be deleted are rows that at one time contained data. So it is not necessary to open your sheets and delete the rows at the bottom each day. 

 

For example, I just opened a sheet that has 23 rows containing data, then there are blank new rows up to row 50. If I delete rows 24 - 50, exit the sheet, and open the sheet, the blank rows at the bottom will come back. This allows users to add data to their sheets without having to insert rows. These are new rows and will auto fill formulas, web form entries will be added to them, etc. 

 

The only rows you should delete are rows that have contained data. It would be a best practice to delete a row rather than deleting the content of the row. 

 

Does this help clarify? 

David Bright

Hi Travis,

Thanks for submitting the enhancement request. Much appreciated.

 

Yes, I totally understand what you are saying, thanks.

 

What I'm also saying is that even though thise are "new" rows, for some reason in my sheets, the results are inconsistent. Not sure why this is, but it happens.

 

Anyway, I appreciate all your help and suggestions, and hope the product team will consider adding the enhanacement.

Thansk and regards,

David

Loann McGee

Reading through this thread, I went on a search and found in the Smartsheet Formula Examples page and entry on line 15 that might apply. It says adding a formula that references the entire column will allow your formula to automatically extend as you add new rows to your sheet. This is the sample formula: =SUM([Value 1]:[Value 1])

However, I'm wondering what the formula would be if I'm simply wanting it to auto-populate something as simple as a question mark in the column. I have three columns that needs to have a ? mark in it as a visual reminder that this column needs updated. This is what I used for the "Test Formula" column I created, but I get the error #MISSING OR INVALID PARAMETERS.

="?"([Test Formula]:[Test Formula])

#FormulasArentMyForte

Blessings,

Loann

 

Travis

Loann, try adding this formula to the cells with the question mark:

 

="?"

 

Add this to each cell in the last three rows in your sheet where you want a ?. Now, when you add a new row, the formula (and ?) will be auto-populated on the new rows.

Loann McGee

I have had that in place, but it seems to not always pick up the formula and carry it on. Not sure why, even after reading through this whole thread. I was hoping there was a way to do the entire column formula with it.

Thanks, Travis!

David Bright

Yes, Loann... this is my experience also... it's super frustrating because it is inconsistent. It would work better for me if there was the ability to set the formula at the column header level as an option.

Loann McGee

It does work -  as long as someone doesn't inadvertantly erase the formula. I've had that happen several times (even though I've alerted people to the formulas).

Humans - aren't we a sorry, but beautiful, lot? ;-)

Happy Monday - we're bracing for what we hope to be our first winter storm - and it's to be several inches or even a foot or more - woo-hoo!!!! I LOVE SNOW!!!

Loann

Jeremy Myers

RE: Autofill Functionality: I am also asking for the same functionality  as David Bright

 

There are a number of Rube Goldberg work arounds suggested and yeah, they work but not in a 'production' type environment with lots of users.

 

Please implement a new feature in Smartsheet that allows a sheet Owner or Admin to define a formula at the "column header" level.   That formula applies to all cells in that column, irrespective of if it is a "new" row, or a "blank" row... I.e. the formula is always there and can never be deleted.

 

 

Travis

Thanks for the suggestion, Jeremy. I will add your vote for this!