Retaining metrics for each month

[Handy]

What condition can be used to keep a cells evaluation status within that
month even if it changes the next month. For example if A2 = "evaluated" in
January but changes to "completed" in February, how can you retain that
evaluated status for the month of January even after it changes during a
later month?

 

[Dave Peterson]

You'll need something (you???) to change the formula to a value.

Depending on what you're doing, you may be able to use a worksheet_calculate
event.

After each calculation, the range you specify would be examined. If the formula
evaluates to "completed", then the macro would change the formula to a value.

 

[Handy]

How would I do that?
I'll use this as an example:
Column B represents status (evaluated, completed)...
January month end metrics need to show how many (within column B) show
evaluated during the month of January. Lets say that calculates to 5 total
within column B that have a status of "evaluated".
Now for February's month end metrics, I need to know how many show evaluated
and it calculates to 3 total within column B that have a status of
"evaluated".

BUT now that formula would have changed January's total as well....
What exactly would I need to do in this situation? How would I change a
formula to a value?

 

[Dave Peterson]

Manually, you can just copy|paste special|values.

In code, it's
with worksheets("Sheet1")
.value = .value
end with

I don't think I understand enough about your workbook to offer any specific
instruction.

 

[Dave Peterson]

Correcting a typo...

with worksheets("Sheet1").range("A1")
.value = .value
end with