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.

<colgroup><col><col><col span="10"><col></colgroup><tbody></tbody>

9	10	11	12	1	2	3	4	5	6	7	8
September	October	November	December	January	February	March	April	May	June	July	August
0	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE
7	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE
0	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE

<colgroup><col><col span="10"><col></colgroup><tbody>
</tbody>

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