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

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!

Best Answers

  • Automations 1
    Automations 1 ✭✭✭✭✭
    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"))

Answers

  • Automations 1
    Automations 1 ✭✭✭✭✭

    Hi @Ramzi K


    Thanks, your solution for #1 worked!


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

  • Ramzi K
    Ramzi K ✭✭✭✭✭

    @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: [email protected]

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

  • Automations 1
    Automations 1 ✭✭✭✭✭
    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"))

  • Ramzi K
    Ramzi K ✭✭✭✭✭

    @Rivky Emert

    Great job!

    Cheers,

    Ramzi

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

    Feel free to email me: [email protected]

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

  • Erica Cole
    Erica Cole ✭✭✭✭

    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?

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭

    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:[email protected] | P: +46 (0) - 72 - 510 99 35

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

  • Erica Cole
    Erica Cole ✭✭✭✭

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

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!