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 mock-up 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 right-click 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 mock-up 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 co-worker 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 right-click 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
- Smartsheet Customer Resources
- 64.1K Get Help
- 414 Global Discussions
- 221 Industry Talk
- 460 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 141 Just for fun
- 58 Community Job Board
- 462 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 299 Events
- 38 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!
