Rolling up a snapshot of data by month

[Mercdoodle]

I need to know a way to capture a snapshot of data as of the date entered
into column B each month. The problem is that as items are "Closed", the
report for the previous month's data changes. Here's my report spreadsheet:

A: Month
B: Date Checked
C: Total Submitted to date
D: # Completed at check (date that is entered into column B)
E: % Completed at check

These are rolling totals based on what is entered in the "Data Entry" sheet,
but I do not want them dynamic. I want January's data to stay January's
data, even if something is "Closed" in February (it would then count in
February's totals).

Formulas are as follows:
C: =IF(ISBLANK(B3),"",COUNTIF('Data Entry'!$B$2:$B$2000,"<="&(B3)))
D: =IF(ISBLANK(B3),"",SUMPRODUCT(--('Data
Entry'!$F$2:$F$2000="Closed"),--('Data Entry'!$B$2:$B$2000<=(B3))))
E: =IF(ISBLANK(B3),"",(D3/C3))

Any ideas?

 

[smartin]

I need to know a way to capture a snapshot of data as of the date entered
into column B each month. The problem is that as items are "Closed", the
report for the previous month's data changes. Here's my report spreadsheet:

A: Month
B: Date Checked
C: Total Submitted to date
D: # Completed at check (date that is entered into column B)
E: % Completed at check

These are rolling totals based on what is entered in the "Data Entry" sheet,
but I do not want them dynamic. I want January's data to stay January's
data, even if something is "Closed" in February (it would then count in
February's totals).

Formulas are as follows:
C: =IF(ISBLANK(B3),"",COUNTIF('Data Entry'!$B$2:$B$2000,"<="&(B3)))
D: =IF(ISBLANK(B3),"",SUMPRODUCT(--('Data
Entry'!$F$2:$F$2000="Closed"),--('Data Entry'!$B$2:$B$2000<=(B3))))
E: =IF(ISBLANK(B3),"",(D3/C3))

Any ideas?

What you really need is the calendar day (or month) of activity in your
source data. Then you can get the correct calendar month summary as you
seek.

If that is not an option, you need to "lock down" each month, e.g., by
replacing the formulas with values (copy/paste special|values). But
unless you know exactly what the data looked like at month-end, you
might not be able to recreate prior month totals now.