I am trying to chart progress daily and to keep the value of each day's
progress.

A1 has the sum of column C1:C22 that is assigned points for progress. This
value changes as progress is made. Assume A1 is 10 on the 06/23 and 15 on
the 06/24.

A2 is Today().

Column B1:B22 is a date range from today to 22 days later.

Column D122 is for progress

How do create a formula that when A2=B1 then D1 is A1 (06/23=06/23 then D1=
10), the next day when I open the file 06/24 the value for D1 is still 10 and
the value for D2 is 15?

The IF statement fails to work since it will update each day with new
values. My attempt was:

In cell D1: =IF(B1>=$A$2, $A$1,#N/A). This works for getting the value but
old data is written over. How/what can you do to capture the data and not
lose it?

Untested, but the following, when run daily, should do the trick (or get you
close).

Sub Capture_Progress()

Dim currentcell As Range
Set currentcell = Range("B1")

Do
If Range("A2").Value = currentcell.Value Then
currentcell.Offset(0, 2) = Range ("A1").Value
Exit Do
End If
Set currentcell = currentcell.Offset(1, 0)
Loop

End Sub

"EO" wrote:

> I am trying to chart progress daily and to keep the value of each day's
> progress.
>
> A1 has the sum of column C1:C22 that is assigned points for progress. This
> value changes as progress is made. Assume A1 is 10 on the 06/23 and 15 on
> the 06/24.
>
> A2 is Today().
>
> Column B1:B22 is a date range from today to 22 days later.
>
> Column D122 is for progress
>
> How do create a formula that when A2=B1 then D1 is A1 (06/23=06/23 then D1=
> 10), the next day when I open the file 06/24 the value for D1 is still 10 and
> the value for D2 is 15?
>
> The IF statement fails to work since it will update each day with new
> values. My attempt was:
>
> In cell D1: =IF(B1>=$A$2, $A$1,#N/A). This works for getting the value but
> old data is written over. How/what can you do to capture the data and not
> lose it?

I read up on Offset() but can not make sense of the rest of the instruction
below. Please simplfy if possible, thanks.

"StumpedAgain" wrote:

> Untested, but the following, when run daily, should do the trick (or get you
> close).
>
> Sub Capture_Progress()
>
> Dim currentcell As Range
> Set currentcell = Range("B1")
>
> Do
> If Range("A2").Value = currentcell.Value Then
> currentcell.Offset(0, 2) = Range ("A1").Value
> Exit Do
> End If
> Set currentcell = currentcell.Offset(1, 0)
> Loop
>
> End Sub
>
> "EO" wrote:
>
> > I am trying to chart progress daily and to keep the value of each day's
> > progress.
> >
> > A1 has the sum of column C1:C22 that is assigned points for progress. This
> > value changes as progress is made. Assume A1 is 10 on the 06/23 and 15 on
> > the 06/24.
> >
> > A2 is Today().
> >
> > Column B1:B22 is a date range from today to 22 days later.
> >
> > Column D122 is for progress
> >
> > How do create a formula that when A2=B1 then D1 is A1 (06/23=06/23 then D1=
> > 10), the next day when I open the file 06/24 the value for D1 is still 10 and
> > the value for D2 is 15?
> >
> > The IF statement fails to work since it will update each day with new
> > values. My attempt was:
> >
> > In cell D1: =IF(B1>=$A$2, $A$1,#N/A). This works for getting the value but
> > old data is written over. How/what can you do to capture the data and not
> > lose it?