Formula Only Column

ZachTenHaken

Hello!

Can the entire column have the formula in each cell without it marking it as a new row?

 

Status Column = Drop-down Column

State Column = Symbols Column (with red/yellow/green symbols)

 

Goal: State column automatically updates based on Status column. 

 

Example:  If Status column says “sent for approval” then the my State column has a formula (not exact formula, just used for detail):

• =IF(State Column 1 = “Sent for approval”, “Yellow”, IF(State Column 1 = “Approved”, “Green”, “Red”)),

 

Can this be in every cell in that column by default or do I always manually have to copy this formula to each new row added? 

 

Thanks!

 

Zach

Chelsea Smith

If you have 2 instances of the same formula in 2 rows, new rows below them will have the formula if they are added via a form. 

Also, in forms you can have a default value, but it can't be a formula (will just read formula as text). 

This, along with a default cell value (formula or otherwise) would be so helpful!   I have been requesting for years, unfortunately.  

This would be a great add, maybe they will announce something like this at Engage.

Andrée Starå

Hi Zach,

If the rows are coming after each other (at least two) they formula would autofill.

Would that work?

Hope that helps!

Have a fantastic week!

Best,

Andrée Starå

Workflow Consultant / CEO @ WORK BOLD

ZachTenHaken

Chelsea, 

Thanks! I didn't realize that if there was 2 rows with the same formula that it would automatically continue the pattern. 

I guess I'll have two "locked rows" at the top of my sheet with all my formulas so that they carry throughout the sheet.....? 

Thanks for the help!!

Zach

Andrée Starå

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

Paul Newcome

I will echo the above suggestions of two rows allowing the use of the auto-fill feature. The auto-fill will also work even without using a form entry. If you have formulas in a column (and at least two rows filled above that already), you can manually start entering data in the next unused row and the formulas will still auto-fill.

 

I generally setup my sheets that are used like this with two locked rows at the top. I grey them out first. Then I enter something along the lines of "This row used for formatting" or "Header Row" in all of the "non-formula" columns or something along those lines to let people know those rows need to be there.

Andrée Starå

Sometimes I also set a default filter that hides the starting rows. 

Paul Newcome

I hadn't thought of that.