Formula to capture data in a cell on last day of the month.

Tina Ciak

I'm trying to figure out a formula that will capture the data ($) that is in a cell (on another sheet) on the last day of each month. Is this even possible?

These are items that will move/change frequently so I just need to lock in the numbers at the end of each month and would prefer it to not have to be manual.

Can anyone help?

Andrée Starå

Hi Tina,

Will the cell change after the last day of the month.

Can you describe your process in more detail and maybe share the sheet(s) or some screenshots? (Delete/replace any confidential/sensitive information before sharing) That would make it easier to help. (share too, andree@getdone.se)

Hope that helps!

Have a fantastic day!

Best,

Andrée Starå

Workflow Consultant / CEO @ WORK BOLD

Debbie Sawyer

Hi

Can it be overwritten on the last day of the next month?

i.e. so it will always show the value from the last day of the previous month (if you see what I mean).

Kind regards

Debbie

L_123

=date(year(today()),month(today()),1)-1

The above will show the last day of the previous month. You can use many different variations of this formula to achieve different things in smartsheet. Depending on how you have your sheet arranged, if you have a date column that shows every date in the year, you could do something like the below

=index([Return KPI]:[Return KPI], match(date(year(today()),Month(today()),1)-1,Date:Date,0))

this will return the value in the "Return KPI" column that corresponds to the last day of the previous month.

Tina Ciak

Hi everyone,

Thanks for your comments above. It's taken me a while to get back to this-- I am temporarily using a smartsheet workflow where it sends me a snapshot of the totals at the end of each month.

Let me try to explain a little better.

We have a savings pipeline with 5 lanes. We need to capture what the total is in each lane in dollars at the end of each month. These totals are constantly changing, so it's imperative that I capture the total at the end of the day on the last working day of the month so I can report that data to my team. So far I have not found a way to pull this info in and just lock it. If I use a formula, the numbers will continue to change, if I use cell linking, the numbers will continue to change. I'm hoping to find a way to fill in my chart without having to do it manually each month. Am I missing something?

Andrée Starå

Hi @Tina@Masco,

Would it work to copy the rows automatically to another sheet instead?

Best,

Andrée

Paul Newcome

I was thinking the same thing. A date type column to establish the last day of the month drives the trigger for a copy row automation.