Retain final figure as month changes.

[IanB76]

Hi,

I have a formula that reads from a cell based on the month we are in. the cell it reads from is a constantly changing figure throughout the course of the month.


=IF(C3=MONTH(TODAY()), $C$16)

The formula works perfectly fine, however, the problem lies when we move to the next month, the figures will become false for September.

I need to somehow retain the last figure of the month so the chart that runs off this doesn't keep zeroing itself.

[TABLE="width: 838"]
<colgroup><col><col><col span="10"><col></colgroup><tbody></tbody>[/TABLE]
[TABLE="width: 774"]
<colgroup><col><col span="10"><col></colgroup><tbody>[TR]
[TD="align: right"]9[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]11[/TD]
[TD="align: right"]12[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]8[/TD]
[/TR]
[TR]
[TD]September[/TD]
[TD]October[/TD]
[TD]November[/TD]
[TD]December[/TD]
[TD]January[/TD]
[TD]February[/TD]
[TD]March[/TD]
[TD]April[/TD]
[TD]May[/TD]
[TD]June[/TD]
[TD]July[/TD]
[TD]August[/TD]
[/TR]
[TR]
[TD="align: right"]0[/TD]
[TD="align: center"]FALSE[/TD]
[TD="align: center"]FALSE[/TD]
[TD="align: center"]FALSE[/TD]
[TD="align: center"]FALSE[/TD]
[TD="align: center"]FALSE[/TD]
[TD="align: center"]FALSE[/TD]
[TD="align: center"]FALSE[/TD]
[TD="align: center"]FALSE[/TD]
[TD="align: center"]FALSE[/TD]
[TD="align: center"]FALSE[/TD]
[TD="align: center"]FALSE[/TD]
[/TR]
[TR]
[TD="align: right"]7[/TD]
[TD="align: center"]FALSE[/TD]
[TD="align: center"]FALSE[/TD]
[TD="align: center"]FALSE[/TD]
[TD="align: center"]FALSE[/TD]
[TD="align: center"]FALSE[/TD]
[TD="align: center"]FALSE[/TD]
[TD="align: center"]FALSE[/TD]
[TD="align: center"]FALSE[/TD]
[TD="align: center"]FALSE[/TD]
[TD="align: center"]FALSE[/TD]
[TD="align: center"]FALSE[/TD]
[/TR]
[TR]
[TD="align: right"]0[/TD]
[TD="align: center"]FALSE[/TD]
[TD="align: center"]FALSE[/TD]
[TD="align: center"]FALSE[/TD]
[TD="align: center"]FALSE[/TD]
[TD="align: center"]FALSE[/TD]
[TD="align: center"]FALSE[/TD]
[TD="align: center"]FALSE[/TD]
[TD="align: center"]FALSE[/TD]
[TD="align: center"]FALSE[/TD]
[TD="align: center"]FALSE[/TD]
[TD="align: center"]FALSE[/TD]
[/TR]
</tbody>[/TABLE]


I keep ending up with circular references or just a broken formula in various different ways.


Any help is greatly appreciated

 

[IanB76]

For the current year you could do something like this:

=SUMIFS(Required!Q11:Q842, Required!E11:E842,">=" & DATE(YEAR(TODAY()),9,1), Required!E11:E842,"<" & DATE(YEAR(TODAY()),10,1),Required!I11:I842,"<>",Required!N11:N842,"Complete", Required!P11:P842,"<>" )+($F$16)

i.e. greater than or equal to the 1st September and less that the 1st October.

That seems to have got it .. Brilliant Thanks FormR

 

[FormR]

Glad to help, welcome to the forum BTW

 

[IanB76]

Glad to help, welcome to the forum BTW

Thankyou very much, is good to be a part of it





I have had to cell ref: the date due to working Aug to Aug, i am going to hit a year shift come Jan, final formula below for if anyone need the reference.


=SUMIFS(Required!$Q$11:$Q$842, Required!$E$11:$E$842,">=" &DATE(D2,9,1), Required!$E$11:$E$842,"<" & DATE(D2,10,1), Required!$I$11:$I$842,"<>",Required!$N$11:$N$842,"Complete", Required!$P$11:$P$842,"<>" )+($F$16)




Cheers