Trailing 12 month Table (Issue while using OFFSET + COUNT function)

[Brian George]

Dear,

I have to create a table to include formulas to retrieve the past 12 months data from another table what has data from Jan-15 to Feb-16.
I want the new table to record the recent 12 months data automatically.

I tried using the OFFSET function with count function but it doesn't seem to work when there is a blank cell.

I've attached the workbook along : Password: 189114
My reference table has its headers(months) from cell B9 to Y9, data obtained by formulas spread through B10 to Y15.

I appreciate any help.
Thanks in advance.

 

[p45cal]

Didn't seem to be too bad.
All I've done is make the formulae all depend on row 10.
formulae adjusted in cells B20:m25 of tables sheet - if I've got the right table.

 

[bosco_yip]

1] Formulae amendment are making to Consolidated Table and 12 Month Rolling Table (Row 9 to Row25)

2] Name of A10: A15 and A20:A25 are used according to the OBS Data Sheet name, so that they can be used as cell ref.

3] In B10, formula copy across and down :

=COUNTIFS('OBS Data'!$C:$C,"<="&DATE(YEAR(B$9),MONTH(B$9)+1,0),'OBS Data'!$C:$C,">="&B$9,'OBS Data'!$P:$P,$A10)

4] In B14, formula copy across :

=IF(B10+B11,B11/(B10+B11),"")

5] In B15, formula copy across :

=IF(SUM(B10:B12)>0,AVERAGEIFS('OBS Data'!$S:$S,'OBS Data'!$C:$C,"<="&DATE(YEAR(B$9),MONTH(B$9)+1,0),'OBS Data'!$C:$C,">="&B$9),"")

6] In B20, formula copy across and down :

=OFFSET($A$8,MATCH($A20,$A$9:$A$15,0),INDEX(MATCH(2,1/($B$11:$Y$11>0)),)-12+COLUMNS($A1:A1))

Remark :

1] 12 Mth Rolling Table last cell is based on Row 11

2] Custom Cell Formatting are used to remove 0,
and in the Type box enter : 0;;;

Regards
Bosco

 

[Brian George]

Didn't seem to be too bad.
All I've done is make the formulae all depend on row 10.
formulae adjusted in cells B20:m25 of tables sheet - if I've got the right table.

It worked. Thanks a lot.

 

[Brian George]

1] Formulae amendment are making to Consolidated Table and 12 Month Rolling Table (Row 9 to Row25)

2] Name of A10: A15 and A20:A25 are used according to the OBS Data Sheet name, so that they can be used as cell ref.

3] In B10, formula copy across and down :

=COUNTIFS('OBS Data'!$C:$C,"<="&DATE(YEAR(B$9),MONTH(B$9)+1,0),'OBS Data'!$C:$C,">="&B$9,'OBS Data'!$P:$P,$A10)

4] In B14, formula copy across :

=IF(B10+B11,B11/(B10+B11),"")

5] In B15, formula copy across :

=IF(SUM(B10:B12)>0,AVERAGEIFS('OBS Data'!$S:$S,'OBS Data'!$C:$C,"<="&DATE(YEAR(B$9),MONTH(B$9)+1,0),'OBS Data'!$C:$C,">="&B$9),"")

6] In B20, formula copy across and down :

=OFFSET($A$8,MATCH($A20,$A$9:$A$15,0),INDEX(MATCH(2,1/($B$11:$Y$11>0)),)-12+COLUMNS($A1:A1))

Remark :

1] 12 Mth Rolling Table last cell is based on Row 11

2] Custom Cell Formatting are used to remove 0,
and in the Type box enter : 0;;;

Regards
Bosco

I tried, it works fine with the data present, but when a new data (eg. Mar-16) is entered it doesn't seem to work.
I appreciate your help. Thanks.

 

[bosco_yip]

I tried, it works fine with the data present, but when a new data (eg. Mar-16) is entered it doesn't seem to work.
I appreciate your help. Thanks.

The formulae are given can move auto if a new data added, please give copy of file for me to test.

Regards

 

[Brian George]

The formulae are given can move auto if a new data added, please give copy of file for me to test.

Regards

I've attached the copy you've send me.

 

[bosco_yip]

I've attached the copy you've send me.

1] Your attached file Formula Calculation was set in Manual, I switched it back into Automatic.

2] In the OBS Data Sheet, I add 4 lines of new data for the month of March-2016 for testing purpose.

Regards
Bosco