Formula result memory

[Branagorn]

I've been using excel for a few years now, I'm a little familiar with alot of things, but perhaps rusty when it comes to more indepth/technical stuff. Macros and VBA dont completely scare me, but I couldnt begin to start writing my own code from scratch.

I'm going to have a large database of data fields being populated over time. It will be much larger, but lets say for examples sake, they would cover a field of A1:G18. (Perhaps in future either I'll make this field either the maximum excel offers, or find a way for it to cleverly expand itself to the last row with data entered).

My task is to create a Dashboard that monitors the gathering of this data. This could be text, numbers, dates, not relevent what it is. For now I am just concerning myself with completed cells. Using COUNTA(A1:G18) for now. Simple

What I want to be able to do, is monitor and assess the gathering of this data over time. So I was thinking, is there a way to get the spreadsheet to assess in a column on a seperate sheet. What the overall number of completed cells was, at the end of each month, or week.

Maybe this idea requires a completely different approach to what Im thinking, but my first thought is something like.

[TABLE="width: 500"]
<tbody>[TR]
[TD]Oct 14:
[/TD]
[TD]Formula that checks if today is after October, If it is, saves the value (COUNTA(A1:G18), dosent change in future / Removes cell entry as being a formula
[/TD]
[/TR]
[TR]
[TD]Nov 14:
[/TD]
[TD]Formula that checks if today is after November, If it is, saves the value (COUNTA(A1:G18), dosent change in future / Removes cell entry as being a formula
[/TD]
[/TR]
[TR]
[TD]Dec 14:
[/TD]
[TD]Formula that checks if today is after December, If it is, saves the value (COUNTA(A1:G18), dosent change in future / Removes cell entry as being a formula
[/TD]
[/TR]
[TR]
[TD]Jan 15:
[/TD]
[TD]Formula that checks if today is after January, If it is, saves the value (COUNTA(A1:G18), dosent change in future / Removes cell entry as being a formula
[/TD]
[/TR]
[TR]
[TD]Feb 15:
[/TD]
[TD]Formula that checks if today is after February, If it is, saves the value (COUNTA(A1:G18), dosent change in future / Removes cell entry as being a formula
[/TD]
[/TR]
[TR]
[TD]Mar 15:
[/TD]
[TD]Formula that checks if today is after March, If it is, saves the value (COUNTA(A1:G18), dosent change in future / Removes cell entry as being a formula
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Maybe being clever, the static dates in Column A could be replaced with changing ones like with

=DATE(YEAR(TODAY()),MONTH(TODAY())-6,DAY(TODAY()))
=DATE(YEAR(TODAY()),MONTH(TODAY())-5,DAY(TODAY()))
=DATE(YEAR(TODAY()),MONTH(TODAY())-4,DAY(TODAY()))
=DATE(YEAR(TODAY()),MONTH(TODAY())-3,DAY(TODAY()))

etc. But I wouldnt know if that could be incorporated with the formulas next to them.

Any help you guys could provide would be very much appreciated.

Always happy to explain myself further as Im sure perhaps my description may be hazy.

Thanks

 

[jumbledore]

I think you need vba for this

 

[Branagorn]

I've been working on this and discovered that I can use circular references to check if today lies between two dates, then either update the value accordingly, or leave the value unchanged. like so
=IF(AND(TODAY()>=B11,TODAY()<=B12),COUNTA(A1:G18),D9)

note, this formula is entered into D9, and Cells B11 and B12 contain 01/04/15 and 20/03/15 respectively. These have been entered in manually for test purposes. And so far it works

I would now like to find a way to test if TODAY() lies within the months over a 6 month history (obviously only one of these cases will be true) down 6 rows, in order for me to analyse the progression of data collection. i.e, remove the need for manually entered dated cells.

[TABLE="width: 697"]
<tbody>[TR]
[TD][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
</tbody><colgroup><col><col><col></colgroup>[/TABLE]