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?

Find more posts tagged with

Accepted answers

Kelly Moore

@maynardg

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

Kelly Moore

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

Kelly Moore

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

Kelly Moore

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

Kelly Moore

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.

https://help.smartsheet.com/articles/2481944-set-formulas-for-all-rows-with-column-formulas

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

Kelly Moore

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.

Kelly Moore

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.

Kelly Moore

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

Kelly Moore

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

Kelly Moore

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

All comments

Kelly Moore

@maynardg

cheers,

Kelly

maynardg

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?

Kelly Moore

Status Formula

=IF(Resolved@row=1, "Complete")

%Complete Formula

=IF(Resolved@row=1, "Full")

Do these work for you?

cheers,

Kelly

maynardg

Oh thank you so much!

I will try that, I will let you know.

maynardg

Sorry

Where and how do I do this part?

Status Formula

=IF(Resolved@row=1, "Complete")

%Complete Formula

=IF(Resolved@row=1, "Full")

Kelly Moore

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

maynardg

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

Screen Shot 2021-02-25 at 10.10.35 PM.png

Screen Shot 2021-02-25 at 10.08.39 PM.png

Kelly Moore

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

maynardg

They are both working!

Kelly Moore

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.

https://help.smartsheet.com/articles/2481944-set-formulas-for-all-rows-with-column-formulas

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

maynardg

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.

:( No

Thank you for you patience .

Yes that is what I am doing

I took a picture of the screen

Screen Shot 2021-02-25 at 11.25.48 PM.png

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.