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

  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭
    Answer ✓

    @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
    Kelly Moore ✭✭✭✭✭✭
    Answer ✓

    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
    Kelly Moore ✭✭✭✭✭✭
    Answer ✓

    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
    Kelly Moore ✭✭✭✭✭✭
    Answer ✓

    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
    Kelly Moore ✭✭✭✭✭✭
    Answer ✓

    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

  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭
    Answer ✓

    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
    Kelly Moore ✭✭✭✭✭✭
    Answer ✓

    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
    Kelly Moore ✭✭✭✭✭✭
    Answer ✓

    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
    Kelly Moore ✭✭✭✭✭✭
    Answer ✓

    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
    Kelly Moore ✭✭✭✭✭✭
    Answer ✓

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

«1

Answers

  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭
    Answer ✓

    @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

  • 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
    Kelly Moore ✭✭✭✭✭✭
    Answer ✓

    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")

  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭
    Answer ✓

    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



  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭
    Answer ✓

    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!

  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭
    Answer ✓

    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.

  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭
    Answer ✓

    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
    Kelly Moore ✭✭✭✭✭✭
    Answer ✓

    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

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!