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

Automations 1

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!

Ramzi K

@Rivky Emert

For #1 here's one way to do it:

Helper Column has value 1 in every cell.

Row# Column has has the formula =SUM(Helper@row:Helper$1)

Your formula to get the last value in Column 2 would be: =INDEX([Column 2]:[Column 2], MAX(COLLECT([Row#]:[Row#], [Column 2]:[Column 2], <>"")))

For #2 try adding conditions to your COLLECT.

I can help you more if you have screen shots or sheets to share.

I hope this helps.

Cheers,

Ramzi

Automations 1

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

@Rivky Emert

For #1 here's one way to do it:

Helper Column has value 1 in every cell.

Row# Column has has the formula =SUM(Helper@row:Helper$1)

Your formula to get the last value in Column 2 would be: =INDEX([Column 2]:[Column 2], MAX(COLLECT([Row#]:[Row#], [Column 2]:[Column 2], <>"")))

For #2 try adding conditions to your COLLECT.

I can help you more if you have screen shots or sheets to share.

I hope this helps.

Cheers,

Ramzi

Automations 1

#2.JPG

Hi @Ramzi K

Thanks, your solution for #1 worked!

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

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

Automations 1

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

@Rivky Emert

Great job!

Cheers,

Ramzi

Erica Cole

https://community.smartsheet.com/discussion/comment/265749#Comment_265749

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å

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!

Erica Cole

https://community.smartsheet.com/discussion/comment/321891#Comment_321891

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