can we do this?
I have a smartsheet that I would like the following steps to take place automatically.
I have the following columns
Percent Complete column which has symbols in it.
Resolved column which is a check mark.
Completed column that is a date.
I would like to do a function in one of the columns in the row and have the other 2 columns automatically complete at the same time without manually going into each of the columns.
Can that be done?
Best Answers

Yes. It would be done by placing a formula in each of the columns that you wish to change. The community will need details on what you wish to do  what is the trigger that makes the columns change and what do you want each column to change to in order to help you with the formulas. As always, a screenshot or mockup table where we can see how the data is arranged tremendously helps the community help you. We await your details before we can move you forward.
cheers,
Kelly

Status Formula
=IF(Resolved@row=1, "Complete")
%Complete Formula
=IF(Resolved@row=1, "Full")
For the Completed Date, I would suggest the new Automation, Record Date. There isn't a function that inserts a Date Stamp or the excel equivalent of Now(). The Date is the easiest piece to grab. The Modified Date Stamp is a possibility however many activities change that datestamp and it can be tricky to capture that instance.
Do these work for you?
cheers,
Kelly

If I understood you correctly, you should be able to directly copy the formula that I gave you here and paste it into the top cell of either your Status (or whatever you call that column) and your % complete (or whatever you call that column). Be sure that the column Resolved is exactly how you named your resolved column. If it's different, change the name to exactly (case and spelling) match yours. This is where that screenshot is useful when the community helps anyone.
For the last column, you will go to the automation menu and, using the drop down, select Record a date. Build that automation as I have shown in the screenshot.
If you need more details, help me understand where you are stuck. Let me know how you are coming along.
Kelly

In the status column, paste this formula by copying it from here
=IF(Resolved@row=1, "Complete")
We can then drag it down the column once we're sure it's doing what you want, we'll make it more robust by clicking a button and converting it to a column formula.
In the Percent Complete column, copy/paste this formula
=IF(Resolved@row=1, "Full")
For the Completed Date column, we'll build the automation. Let's leave that for a moment until you get these others working.
Let me know how you do. We'll build this step by step.
Kelly

Fantastic. Now to make them a 'column formula', right click in the cell. The column menu should be displayed. At the very bottom of this list is 'Convert to Column Formula'. Click that. You'll have to do this for both columns individually. Once you do this, this formula will be in every cell in that column automatically.
Here is more information on column formulas. Note to edit this formula, you will have to right click and turn the column formula back off the same way you just turned it on.
For the Completed Date, are you clear how to build the automation? We can do that step by step if you need some more guidance. Let me know.
Kelly

where are you on your sheet that you are right clicking? At the very top of the column, right below the column title, is the cell with the dropdown arrow. Click on this dropdown to see the column menu. See if the words Edit column formula and Convert to CELL formula are present. If yes, click Convert to Cell formula. If that formula is a Column formula, this will toggle it off.

Ok. Let's back up. When you mentioned you see that the formula was used in a dashboard, what column did you try to rightclick in? I wanted you to right click in a cell where you just pasted in a formula. Not a double click but a right click. When you do that, you should see Convert to COLUMN formula at the very bottom of that list. Click that. Do that for both of the formulas you just pasted.

Hit enter or escape so that the formula goes away and you are just left with the word 'Complete' in the cell. Please right click then in that cell. This is what I'm trying to get you to
If you don't have access to this, just drag the formula all the way down the column. Repeat for your Percent complete column

ok, it looks like the formula is all the way down the columns.
Going back to the top where I showed how to create the automation Record a Date, were my instructions clear enough to follow? Try creating the automation. Let me know

The automation was created? that worked? The automation will only happen on new checkmarks.
Answers

Yes. It would be done by placing a formula in each of the columns that you wish to change. The community will need details on what you wish to do  what is the trigger that makes the columns change and what do you want each column to change to in order to help you with the formulas. As always, a screenshot or mockup table where we can see how the data is arranged tremendously helps the community help you. We await your details before we can move you forward.
cheers,
Kelly

Okay I think this is what you want from me.
If Resolved column  type = checked  is checked
Then change Status column  Type = Single Drop Down  to Complete
And change Percentage Complete column  type = Symbols  to Full
And change Completed Date  type = date  to right now
Does that make sense?

Status Formula
=IF(Resolved@row=1, "Complete")
%Complete Formula
=IF(Resolved@row=1, "Full")
For the Completed Date, I would suggest the new Automation, Record Date. There isn't a function that inserts a Date Stamp or the excel equivalent of Now(). The Date is the easiest piece to grab. The Modified Date Stamp is a possibility however many activities change that datestamp and it can be tricky to capture that instance.
Do these work for you?
cheers,
Kelly

Oh thank you so much!
I will try that, I will let you know.

Sorry
Where and how do I do this part?
Status Formula
=IF(Resolved@row=1, "Complete")
%Complete Formula
=IF(Resolved@row=1, "Full")

If I understood you correctly, you should be able to directly copy the formula that I gave you here and paste it into the top cell of either your Status (or whatever you call that column) and your % complete (or whatever you call that column). Be sure that the column Resolved is exactly how you named your resolved column. If it's different, change the name to exactly (case and spelling) match yours. This is where that screenshot is useful when the community helps anyone.
For the last column, you will go to the automation menu and, using the drop down, select Record a date. Build that automation as I have shown in the screenshot.
If you need more details, help me understand where you are stuck. Let me know how you are coming along.
Kelly

I am stuck on putting the 3 lines of the formula in
the Status column.
It is saying it is unparseable?
.Here are the columns

In the status column, paste this formula by copying it from here
=IF(Resolved@row=1, "Complete")
We can then drag it down the column once we're sure it's doing what you want, we'll make it more robust by clicking a button and converting it to a column formula.
In the Percent Complete column, copy/paste this formula
=IF(Resolved@row=1, "Full")
For the Completed Date column, we'll build the automation. Let's leave that for a moment until you get these others working.
Let me know how you do. We'll build this step by step.
Kelly

They are both working!

Fantastic. Now to make them a 'column formula', right click in the cell. The column menu should be displayed. At the very bottom of this list is 'Convert to Column Formula'. Click that. You'll have to do this for both columns individually. Once you do this, this formula will be in every cell in that column automatically.
Here is more information on column formulas. Note to edit this formula, you will have to right click and turn the column formula back off the same way you just turned it on.
For the Completed Date, are you clear how to build the automation? We can do that step by step if you need some more guidance. Let me know.
Kelly

UGH
When I right click there are 2 lines I can't get a screencapture.
the first line is
FX= with the formula
the second line says
Used in formula on Ticket Metrics that my coworker created for the Dashboard.

where are you on your sheet that you are right clicking? At the very top of the column, right below the column title, is the cell with the dropdown arrow. Click on this dropdown to see the column menu. See if the words Edit column formula and Convert to CELL formula are present. If yes, click Convert to Cell formula. If that formula is a Column formula, this will toggle it off.

:( No

Ok. Let's back up. When you mentioned you see that the formula was used in a dashboard, what column did you try to rightclick in? I wanted you to right click in a cell where you just pasted in a formula. Not a double click but a right click. When you do that, you should see Convert to COLUMN formula at the very bottom of that list. Click that. Do that for both of the formulas you just pasted.

Thank you for you patience .
Yes that is what I am doing
I took a picture of the screen
Help Article Resources
Categories
 All Categories
 14 Welcome to the Community
 10.8K Get Help
 65 Global Discussions
 69 Industry Talk
 385 Announcements
 3.6K Ideas & Feature Requests
 56 Brandfolder
 125 Just for fun
 50 Community Job Board
 464 Show & Tell
 40 Member Spotlight
 44 Power Your Process
 28 Sponsor X
 234 Events
 7.3K Forum Archives
Check out the Formula Handbook template!