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

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?

Find more posts tagged with

Formulas

Comments

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

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.

Sign in to join the conversation!

Quick Links

Trending discussions

count if value is between a date range (reference between 2 sheets)
Hi team i have 2 sheets for material inventory. the first is to record witch equipment is ship out for repair and the other is the total of equipment available. the first is set as follow: equipment type, date out, date return, quantity (negative number) the other one is as follow: each row is for a single date with the…
Index Distinct Support
I would like to parse out this data by creating a listing with a row for each person, for each project they appear on, for each month. I think this would only give me the groupings anyways, but this doesnt seem to even get me started - it popped up then people have disappeared.
Lookup Value Issues
I am trying to populate a column from Sheet A into Sheet B and Sheet C. My Lookup column for Sheet A is formatted as such so there is a ' in front to account for ones that have a leading 0 and some do not. Each ID should be 6 digits long. I made a helper column looking at this Dealer ID column to try and get rid of that…