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

Options
✭✭✭✭✭

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!

• ✭✭✭✭✭
Options

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

• ✭✭✭✭✭
Options

Hi @Ramzi K

Thanks, your solution for #1 worked!

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

• ✭✭✭✭✭
Options

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

• ✭✭✭✭✭
Options

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

• ✭✭✭✭✭
Options

Great job!

Cheers,

Ramzi

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

Feel free to email me: ramzi@cedartreeconsulting.com

• ✭✭✭✭
Options

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?

• ✭✭✭✭✭✭
Options

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.

• ✭✭✭✭
Options

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!