Formulas for current month and previous month

Hello all

(See attached screenshot of my sheet)

I need some help. I have a budge/cashflow sheet. From this sheet I would like to create a Dashboard Role-up sheet that gives me the following:

PREVIOUS month's budgeted income

PREVIOUS month's budgeted losses

PREVIOUS month's actual income

PREVIOUS month's actual losses

Cumulative actual losses up to previous month

CURRENT month projected income

CURRENT month projected losses

Is there a way I can write a formula in the role-up sheet for each of the above?

Budget screen shot.PNG

Find more posts tagged with

Formulas

Comments

Genevieve P.

Hi Jana,

Before creating a formula or report that looks for a specific date or month, you would need to have a date column set up in your sheet that it could pull from. Based on the screen shot provided, it looks like your months were manually typed in as headers instead of using a date column.

A potential workaround would be to create a secondary sheet that uses cell-links to shift the information from this source sheet to be set up in a way that a formula could identify. In this second sheet, you could have a date column saying "Month," with each month selected as a row. Your columns would then be "Income Budget" and "Income Actual," etc, pulling in the relevant information, per month, via cell linking.

(See the first screen capture below for an example. I did not use actual cell links but typed in the information based on your image).

You could then create more columns to summarize information per-month, or cell-link in other relevant information from that original source sheet.

Once this secondary sheet is set up as you would like, create a Report based on this sheet that only pulls the relevant information. Since dates in a date column need to have a day attached, and not just a month, I would suggest having each month consistently be noted as the 1st in your secondary sheet. This means you could run the report based on "When," saying that the "Month" column needs to be within the last 62 days. This will pull only the current and previous month's data.

This report could then be embedded into a Dashboard using the Report Widget.

Please let me know if any of this is unclear!

Cheers,

Genevieve

Testing Budget Sheet.png

Sign in to join the conversation!

Quick Links

Trending discussions

How to split contents from a cell from a smartsheet into another smartsheet
I need to extract and split the contents of a cell in a Smartsheet into three separate columns to a new smartsheet. The cell entries are originally submitted through a form.
Information in a form populates specific cell.
Hello, Just investigating an idea. I'm building a staff register sheet. I'm curious to know if its possible to populate specific cells by the answers given in a form? So if, in a form, a staff member gives their name, the date and that they will be on leave, sick, training ect. Once the form is submitted, it will populate…
A multiple SUMIF function
My current formula is below. I would like to update this so that I only get a total of those that have been Quoted and have a timeframe data of 2025. How do I add this extra filter into the formula? =SUMIF(Status:Status, "Quoted", [Est. GP]:[Est. GP])