#### BrutalLogiC

##### Active Member
Hi I'm trying to write a formula in the grey cells which looks at the contract start date in column A and the current month e.g. DA30 then takes the data from the range in yellow and puts it into the corresponding month..... the start dates will be variable so I want the data in the grey cells to move when the start dates are changed

AlanY wrote a great similar formula for me previously but I can't replicate it for this problem, please help

Excel 2013/2016
29Month 0 is month before start monthMonth 1 is contract start date
30Contract Start DateMONTH:01235455Mar-19Apr-19May-19Jun-19Jul-19Aug-19Sep-19Oct-19Nov-19Dec-19Jan-20
3101-07-19-255,200----255,200
3201-07-19------
3301-07-19-3,3003,3003,3003,3003,3003,3003,3003,3003,3003,3003,3003,300
3401-07-193,300-----3,300
3501-07-191,100-----1,100
3601-09-19-61,600----61,600
3701-09-19------
3801-01-20-123,200----123,200
3901-01-20------
Summary

Last edited:

### Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"
Not 100% what you want to total in the yellow boxes but assuming you want to total AS31:CV31. . .

You can enter this formula in cell DA31: IF(MONTH(\$DA\$30)=MONTH(A31),SUM(AS31:CV31),"")

This will extract the month from both fields and look for a match.

If you need to match the month AND the year you can add: IF(AND(MONTH(\$DA\$30)=MONTH(A31),YEAR(\$DA\$30)=YEAR(A31)),SUM(AS31:CV31),"")

hi jmancha thanks for reply but those formulas won't work for me, I'm trying to replicate the currently hard coded values in the grey cells.

The values in yellow should be the same as the ones on the grey but will move dependent on the start date.

If the start date in column A goes from July to August then all the values in grey will move over one column to the right, the values in yellow will be static regardless of the start date.

DE31 has got 255,200 in it because it is under July-19 and A31 says the contract starts in July and AT31 says the month 1 value should be 255,200. If the start date in column A was Aug-19 then the 255,200 would come under August in DF31.

Use the following formula:

Excel Workbook
1
30Contract Start DateMONTH:01235455mar-19abr-19may-19jun-19jul-19ago-19sep-19oct-19nov-19dic-19ene-20
3101/07/2019255,200 255,200
3201/07/2019250250
3301/07/20193,3003,3003,3003,3003,3003,3003,3003,3003,3003,3003,3003,300
3401/07/20193,300
3501/07/20191,100
3601/09/201961,60061,600
3701/09/2019
3801/01/2020123,200123,200
3901/01/2020
Hoja5

The values ??of June 3,300 and 1,000 did not appear in the image, but the formula also finds them, as you can see in the following image:

Excel Workbook
1
30Contract Start DateMONTH:1235455mar-19abr-19may-19jun-19jul-19ago-19sep-19oct-19nov-19dic-19ene-20
3101/07/2019255,200255,200
3201/07/2019250250
3301/07/20193,3003,3003,3003,3003,3003,3003,3003,3003,3003,3003,3003,300
3401/07/20193,3003,300
3501/07/20191,1001,100
3601/09/201961,60061,600
3701/09/2019
3801/01/2020123,200123,200
3901/01/2020
Hoja5

Excellent thanks!

You're welcome!

Replies
2
Views
253
Replies
3
Views
250
Replies
11
Views
365
Replies
7
Views
476
Replies
2
Views
480

1,196,220
Messages
6,014,062
Members
441,801
Latest member
Aneurysm

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back