Formulas for current month and previous month

Jana Brits

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? 

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