Skip to content
  • Try Smartsheet

    • Formulas and Functions

    Formulas and Functions

    Ask for help with your formula and find examples of how others use formulas and functions to solve a similar problem.

    Capture metric each day and add to new row

    ✭✭✭
    in Formulas and Functions

    I have a number of Harvey ball metrics (count of red, yellow, green tasks) and things that change every day. I haven't really seen any way to capture and store these each day at a particular time (or hourly, weekly, monthly). Anyone have a good solution for it?


    Screen Shot of metrics

    Example of metrics I'm collecting on-the-fly from Control Center summary sheets... would love to be able to graph the health over time.. day by day.

    Tags:
    · Share on FacebookShare on Twitter

    Sign in
    Try Smartsheet

    Best Answer

    • Community Champion
      Answer ✓

      The only way currently to convert a cell with a formula to a static cell is the copy/move workflows. You can set up a sheet to receive these, create a column that forces a change every day in the rows you want transferred even if there isn't a change in the data (example would be a =today() formula dragged down) and use that as criteria for the workflow.

      Then base your charts off the historical data sheet.

      · Share on FacebookShare on Twitter

    Answers

    • Community Champion
      Answer ✓

      The only way currently to convert a cell with a formula to a static cell is the copy/move workflows. You can set up a sheet to receive these, create a column that forces a change every day in the rows you want transferred even if there isn't a change in the data (example would be a =today() formula dragged down) and use that as criteria for the workflow.

      Then base your charts off the historical data sheet.

      · Share on FacebookShare on Twitter
    • ✭✭✭

      I had completely forgotten about that workflow! Thanks!

      Built the sheet that captures the data each day, made a couple reports to look at different ranges of time... and added some sample data to make sure it displays what I need.. perfect :)

      Screen Shot 2020-05-14 at 12.00.28 PM.png


      · Share on FacebookShare on Twitter
    • Community Champion

      NP Glad it worked for you.

      · Share on FacebookShare on Twitter

    Sign in
    Try Smartsheet

    Help Article Resources

    Create and edit formulas in Smartsheet

    Formula combinations for cross sheet references

    Smartsheet functions list

    Quick Links

    Categories

    Want to practice working with formulas directly in Smartsheet?

    Check out the Formula Handbook template!

    Trending in Formulas and Functions