Allow editing for cells in a formula column

Troy Clarke

Hello, I have found that when a column is a formula column, you can no longer manually update those cells--ever. Is there a reason these cells need to be "locked down"? When you add a formula, just to the cells manually, and not the entire column, you can always update it whenever you need to. I know I can "turn off" the formula column and change what I need and then add it back to the column, but it does not work 100% the way I need it to, and I just think it would be more useful and efficient to not have it locked down; to allow editing in cells when you add the formula to a column. For me, it would be helpful to allow editing in those cells, because I am finding that for my current intake sheet, I am going backwards with having to manually edit the cells due to the formula lock, so I do not even have the formula in the columns/cells anymore. Side note: regardless if there is a formula column or a formula in a single cell, Dynamic View does not allow updates when any formula is in any cell. Not sure if that was a known issue/glitch, so I wanted to share that here.

ross chambers

I want to be able to be able to have a formula autofill in every row in a column but still be able to edit the cell manually if I need without disabling column formula for every other row

spauliszyn

As a work-around, you can create a helper column with a 'suggested' value, in your official column, you can either enter a value manually or write a 2-second equation that simply references the suggested value.

Eg. =[Suggested Value]@row

However, I currently have a need for this type of functionality. I want certain columns of new line entries to be automatically filled with a result of an equation and not the equation itself. Like a workflow that "Changes the value of a cell" when a "new line is added" but it doesn't just put in a fixed value (like it currently does) but uses the result of a formula that you enter.

dhurjati_sen

I also do have the same requirement as @spauliszyn mentioned above.

When the row is added the formula should fetch the value, but at run time it should allow the value to be edited.

Is there any way around this or any fix/workaround that @smartsheet is coming up with?

Nick055

I have many sheets that the parent rows are formula based but the child task need inputs from users.

Being able to have a column formula to say: If(this is the parent row, Sum(Children()), UserInput (let the user fill out the cell)) then parent rows will always do that formula & child rows can be filled out by users.

Another example is wanting the parent status to be based off the child tasks. Users need to update status at the child level & a formula is needed at the parent level. Example: If(this is the parent row, return the status based on formula, otherwise let the user set the status)

Currently the solution is to make helper columns or to copy the formulas manually.

NicoLHC

@Nick055 does this cost you lot of time ? Can you share a screen shot .

Nick055

Hi Nico,

It ends up costing a good amount of time as I have to teach all my teammates how to maintain the sheet. If this idea was in place, it would be very easy to onboard them to my sheets.

Take a budget sheet for example:

The goal of this sheet is for directors (who don't currently use smartsheets) to maintain budgets per period.

The flow is,

• They fill out a template form which then adds it to this sheet with the amount of child tasks they need.
• That copies to the main sheet where they rename the child tasks to different expense categories.

The parent line of each period is a sum of the child tasks & the user enters each expense category budget. For the picture below: the $310,000 is a formula SUM(CHILDREN()) & $100,000, $200,000, & $10,000 were manually entered.

I would love to set a column formula that says =IF(Parentlevel@row=1(top),SUM(CHILDREN()),USERINPUT)

Another example of this:

This is a screenshot from a sheet that has many projects on it & more are continuously added

The blue in progress is a formula checking:

• If all child tasks are complete - it's complete
• If all child tasks are not started - it's not started
• Otherwise it's In Progress

My team needs to enter the status of the children for all the projects but the parent will always be that formula.

This idea would make a column formula that says IF(Parentlevel=1(Top),use the formula, userinput)

NicoLHC

@Nick055 stupid question can you not make one column with responsibility and filter by current user ?

It’s possible
‘further you can use count ancestor ,count children ….then maybe use a report for the people or department to fill date

Maybe you should change the structure of the sheet depending on the overall purpose and if you want use dashboards report out of the data

NicoLHC

@Nick055

Thank you for your feedback and elaboration. Do you have an action tracker in place to handle deviations?

I would recommend setting up the sheet differently, perhaps more like a structured table. From there, you could apply filters for each person, department, or current user to streamline visibility.

We’ve built similar KPI tracking systems for clients, monitoring weekly KPIs across six locations.

If you’d like, we can schedule a demo to show how it works.

A basic question to consider: What is the final goal? Is it a dashboard for visualizing data, tracking actions on deviations, or primarily data collection?

Or you use this :