How to pre-populate empty cells.

tbittick

Because I haven't found a better place to say this, and because a great deal of this has involved Formulas etc., I wanted to express my gratitude to this community. I'm not usually a community guy mostly because I feel intimidated. But what I'm trying to put together for my company has been important enough I was motivated. I have been helped so graciously I start getting excited about coming back here to be involved.

Having said that... lol

I am working on a tracking system where a form submission sets off a series of workflows which, when triggered will copy a row from one sheet to another. In fact, the form submission sets in motion two of these.

The Challenge:

I'm using a Formula, which I actually got here, to raise a flag anytime the date is less than (older) than today.

It works wonderfully.

My hope was to pre-populate the necessary Flag Columns for several (what I thought might be) empty rows so when the new rows were pasted, the formula would already be in place.

Of course that's not how it works, and not what happened. Each of the rows with the formula in a cell are seen as filled rows and the new rows just get placed at the end and without the formula.

In this image below there is 1) the row with the Formula triggered, 2) an example of a column pre-populated in an otherwise "empty" row and then where the newly pasted row ends up in this scenario.

Not sure if what I'm trying to do is even possible.

Thoughts?

Jeff Reisman

Hi tbittick. I'm new on here as well. Glad I could help you last week. I am finding that helping people on here gives me a chance to figure out some new tricks that may help my own work.

I have run into this formula problem before.

The way Smartsheet deals with continuing formulas from row to row is rule based. The rule is, if a new row is added, and a formula exists in a column in the two rows immediately above or below the new row, Smartsheet auto-populates the formula in the new row.

Where this gets hairy is if there are blank rows in your sheet. If your form is set to place new entries at the top of the sheet, and there are no blank rows at the top (and nobody re-sorts the sheet to put any blanks up there,) you're in good shape. However if you have one or more blank lines at the bottom, and your form places new entries there, the formulas do not copy to the new rows.

So the solution is to remove blank rows, and where possible, have your form submissions add new rows to the top of the sheet.

Jeff Reisman

Hi tbittick. I'm new on here as well. Glad I could help you last week. I am finding that helping people on here gives me a chance to figure out some new tricks that may help my own work.

I have run into this formula problem before.

The way Smartsheet deals with continuing formulas from row to row is rule based. The rule is, if a new row is added, and a formula exists in a column in the two rows immediately above or below the new row, Smartsheet auto-populates the formula in the new row.

Where this gets hairy is if there are blank rows in your sheet. If your form is set to place new entries at the top of the sheet, and there are no blank rows at the top (and nobody re-sorts the sheet to put any blanks up there,) you're in good shape. However if you have one or more blank lines at the bottom, and your form places new entries there, the formulas do not copy to the new rows.

So the solution is to remove blank rows, and where possible, have your form submissions add new rows to the top of the sheet.

Jeff Reisman

I also have found that when using formulas that reference another column on the same row, it's easier to use [Column Name]@row instead of a row number. That way you're not dependent on Smartsheet keeping the auto-numbering straight and you can quickly copy that formula to any row without making any changes.

tbittick

Yeah, I'm kind of a weird bird in my work life anyway. I started life as a newspaper reporter which somehow put me on the cutting edge of the Interwebs (at least here in Michigan 1995), which ultimately led to a life hustling a variety of jobs in the IT world mostly based on my communication, project management, writing and graphic arts experiences.

The result is a completely right-brained guy with a deep rooted fear of math re-inventing himself again (LOL) as a Smartsheet Admin.

I've actually loved it to tell the truth, but yeah, functions and formulas take a little extra attention on my part (okay a lot of extra attention).

Your proposed solution is very interesting if for no other reason than I actually understand it (hey, laughing at myself is how I get by with this!) and am trying to think it through.

For a heartbeat I had the form send to the top of the sheet but I was using the "top row" as a banner. When the new row added to the top, on top of the banner, I dismissed it for visual reasons without seeing what happened to the formula.

There's already a flow that turns the date column red when overdue (the same purpose for raising the flag) so on one hand I don't know that it's necessary. Then again on the other, this seems like a good opportunity to test out something that might likely arise again.

Thank you sir again for your assistance. I'll report back with any interesting updates.

Jeff Reisman

👍️ Anytime! Good luck!

tbittick

So in the image here Rows 2-5 were pre-existing rows all with the formula in the Flag column.

The top row, which is now Row 1, is the new Row that was placed there by a form submission, but does not have the formula in the Flag Column.

I may have misunderstood something in the solution or did something wrong in my testing.

Thoughts?

Jeff Reisman

Sorry for the delayed response, I just saw your latest comment.

Is the Flag column included on your submission form, either as a visible or hidden field? If it is, then remove it from the form. Otherwise, Smartsheet thinks you WANT it to be added with a blank value instead of with the formula it picks up from the rows below.