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.
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
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.
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?
Thanks & Regards,
Dhurjati Sen
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.
@Nick055 does this cost you lot of time ? Can you share a screen shot .
If my comment helps you, I appreciate a 💡
Kind regards
Nico | LinkedIn
CEO | Lighthouse Consultings
Lecturer in Business Information Systems | DHBW
________________________________________________________________________________
addvalue@lighthouseconsultings.com
We offer Licenses - Training - Solution Engineering
🔴Certified Smartsheet Partner _______________________________________________
💯 SCALEABLE Solutions Engineered by Lighthouse Consultings
We Don’t Just Implement Smartsheet; We Revolutionize How You Manage, Plan, And Execute.
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,
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:
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)
@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
If my comment helps you, I appreciate a 💡
Kind regards
Nico | LinkedIn
CEO | Lighthouse Consultings
Lecturer in Business Information Systems | DHBW
________________________________________________________________________________
addvalue@lighthouseconsultings.com
We offer Licenses - Training - Solution Engineering
🔴Certified Smartsheet Partner _______________________________________________
💯 SCALEABLE Solutions Engineered by Lighthouse Consultings
We Don’t Just Implement Smartsheet; We Revolutionize How You Manage, Plan, And Execute.
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 :
If my comment helps you, I appreciate a 💡
Kind regards
Nico | LinkedIn
CEO | Lighthouse Consultings
Lecturer in Business Information Systems | DHBW
________________________________________________________________________________
addvalue@lighthouseconsultings.com
We offer Licenses - Training - Solution Engineering
🔴Certified Smartsheet Partner _______________________________________________
💯 SCALEABLE Solutions Engineered by Lighthouse Consultings
We Don’t Just Implement Smartsheet; We Revolutionize How You Manage, Plan, And Execute.