Automatically Updating Rolling 12 Total Based On Current Date

[ljhami]

Working on a sheet that requires an automatically updating rolling 12 formula for a data set. Background: This will be a projections sheet with all data already filled in through 2020. It will be compared with an imported rolling 12 'actuals' data sheet each month. There's about 100 people in the real table. It won't be me that has to update it so trying to make one less step for the person who does.

To make updating easier each month was hoping to create a formula that will take into fact the current month and automatically shift the rolling 12 based on the column header dates.

For example, it's 2/2019 - I need it take the corresponding data under that header and sum up the past 12 months going back to 3/2018. The next month it will sum up 4/2018-3/2019.

capture.PNG

I've seen where people have mentioned using SUMIFs, OFFSETs, or INDEX/MATCH but it was usually for vertical data and not really sure which one to apply.

[TMS]

Attach a sample workbook (not a picture or pasted copy). Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

[TMS]

Assuming those are dates in row 1, try:

Please Login or Register  to view this content.