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.

    Dashboard Metric that pulls data from the last row and rows with current week.

    ✭✭✭✭✭
    in Formulas and Functions

    Hi,


    I'm working on a dashboard and trying to pull some Metric Info.


    1. I'm looking for a formula that will pull the information that's in the last Row of Column 2, no matter how many rows are entered, I always want the info in the last row, column 2, to appear as a Metric on my dashboard.
    2. On another sheet, I want to pull almost similar to above. However, I want the info to pull only if the date falls out this week. (Usually the last two rows will have a date that falls out this week.)

    Thanks!

    · Share on FacebookShare on Twitter

    Sign in
    Try Smartsheet

    Best Answers

    • ✭✭✭✭✭
      Answer ✓

      Hi @Ramzi K


      I was able to achieve my goal by using the weeknumber formula. (https://help.smartsheet.com/function/weeknumber)

      I created three hidden columns.

      1. Current weeknumber (=WEEKNUMBER(TODAY())
      2. Paydate weeknumber (=Weeknumber(paydate@row))
      3. If statement to determine if the current weeknumber matches the current paydate weeknumber. (=if([current weeknumber]@row=[Paydate weeknumber]@row, "Y","N")

      I then used a sumif with a collect formula in it: =SUM(COLLECT([Amount Paid]:[Amount Paid], [Current Week?]:[Current Week?], "Y"))

      · Share on FacebookShare on Twitter

    Answers

    • ✭✭✭✭✭
      Image
      #2.JPG
      Uploaded 27.78 KB

      Hi @Ramzi K


      Thanks, your solution for #1 worked!


      Here's an image so you can help me out with #2.

      · Share on FacebookShare on Twitter
    • ✭✭✭✭✭

      @Rivky Emert

      So in this case, you would need a bit of a different formula. You want to use a condition in your formula that pulls rows or values where the date falls inside a date range.

      Two options - if your "this week" is "floating", i.e. always =today+7 then you will use [Pay Date]>=TODAY() and [Pay Date]<TODAY(7) in your formula.

      If the week is static (that means Sunday to Monday of the current week, you will need more elaborate formulas or a reference sheet with Start and End dates for all weeks in the year and then use that:

      [Pay Date]>=(Current Week Start) and [Pay Date]<(Current Week End)

      I hope this gets you started.

      Cheers,

      Ramzi

      Ramzi Khuri - Principal Consultant @ Cedar Tree Consulting (www.cedartreeconsulting.com)

      Feel free to email me: ramzi@cedartreeconsulting.com

      💡 If this post helped you out, please help the Community by marking it as the accepted answer/helpful.

      · Share on FacebookShare on Twitter
    • ✭✭✭✭✭
      Answer ✓

      Hi @Ramzi K


      I was able to achieve my goal by using the weeknumber formula. (https://help.smartsheet.com/function/weeknumber)

      I created three hidden columns.

      1. Current weeknumber (=WEEKNUMBER(TODAY())
      2. Paydate weeknumber (=Weeknumber(paydate@row))
      3. If statement to determine if the current weeknumber matches the current paydate weeknumber. (=if([current weeknumber]@row=[Paydate weeknumber]@row, "Y","N")

      I then used a sumif with a collect formula in it: =SUM(COLLECT([Amount Paid]:[Amount Paid], [Current Week?]:[Current Week?], "Y"))

      · Share on FacebookShare on Twitter
    • ✭✭✭✭✭

      @Rivky Emert

      Great job!

      Cheers,

      Ramzi

      Ramzi Khuri - Principal Consultant @ Cedar Tree Consulting (www.cedartreeconsulting.com)

      Feel free to email me: ramzi@cedartreeconsulting.com

      💡 If this post helped you out, please help the Community by marking it as the accepted answer/helpful.

      · Share on FacebookShare on Twitter
    • ✭✭✭✭
      Ramzi K

      Hi Ramzi,

      This has provided me the perfect formula to gather a daily comparison. The only hiccup is I cannot convert =SUM(Helper@row:Helper$1) to a column formula. I get an error that says the "formula syntax isn't quite ight" any idea what I'm doing wrong?

      · Share on FacebookShare on Twitter
    • Community Champion

      Hi @Erica Cole

      I hope you're well and safe!

      It's not possible to use the Column Formula feature when you have a direct cell reference. It has to be @row or # if you're using the Sheet Summary feature.

      Make sense?

      I hope that helps!

      Be safe and have a fantastic week!

      Best,

      Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

      ✅Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

      SMARTSHEET EXPERT CONSULTANT & PARTNER

      Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

      W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

      Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

      · Share on FacebookShare on Twitter
    • ✭✭✭✭
      Andrée Starå

      Is there another formula that can be converted to a column formula that will create the number sequence described above?

      · 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