Display cells 12 months prior and 12 months post from a given date

Hi All,

First time poster so here it goes.

I have an excel spreadsheet (sample attached) which has a list of names and values over a 3 year period (per month).

I have a date and would like to display values for 11 months pre target month, target month and 12 months post target month. Target month being the next month after the date.

Eg. Date 11/09/2011 - Target month being Oct -2011 (the next month after the target date)
Display cells 11months prior to Oct 2011, Oct 2011 and 12 months post target month

There will be times when data post the target month is not available so these should be set to -1 so that the cell can be differentiated to an actual value that is 0.

Hoping this makes sense. If you need clarification just let me know.

Hogsample.xlsx

Hi, Welcome to the forum!

Put this formula in AJ2 and drag it to other cells.

Formula:

Please Login or Register  to view this content.

Thanks Sindhus!!!!

Hi,

Thanks for the response...I think I spoke too soon.

The formula works but I have trouble applying the formula in my worksheet. I think I get part of the formula but not the whole part. When I move the table and apply the formula it returns an erro. I have attached the sample worksheet and how I have applied the formula to a second table. Can you advise where I went wrong and how the formula should be adjust to get the right out in cellBC 25sample 2.xlsx

Thanks
Hog

I have used Column()-47 in AJ column to get -11 as the answer. SO, the first column of your result will give you 11 months prior. In the subsequent columns, it will return -10, -9, etc.

If you are moving it to different column, make sure the first column of your result will give you -11. In the second table of your sheet, the result starts at BC column. So, in the formula replace Column()-47 with COlumn()-66 and drag it to other cells.

Thanks Sindhus...makes sense.