Update value only when ...

[hard2figure]

I am tracking the daily values of a project and want to automate the value
storing. I can already download the values and get them split into the
correct sections/worksheets and I have a running date row but when I setup
formulas to transport “today’s numbers†to “today’s column†I can’t keep the
formulas active for more than a day because when tomorrow comes the cells
will read an error or FALSE.

Is there a way to say IF AF1 = A1, update AF3:AF17 with A3:A17, and if
AF1<A1, leave current value as is?
where A1 is the downloaded date
AF1 is the today in tracking (AE1 was yesterday, AG1 is tomorrow, and so
on)
A3:A17 are the downloaded daily status values to track
AF3:AF17 are where I need A3:A17 to go today
But tomorrow AG1 I want the AF column to not update from the values it
already has stored.

 

[Mike Fogleman]

Here is some code that will do that assuming a couple of things about your
sheet..
1) the dates are already in place across row 1
2) the data will always be in rows 3:17
If either is not true, then the code will need modified.
The code finds the last column in row 1 that has data in it (a date). It
then will scan across row 1 looking for a match to A1. When it finds it it
will use that column to create a new range of rows 3:17 in that column and
make the cell values = to the cells in column A, rows 3:17.
No formulas will be needed in the data area that could error, and the code
will only alter data in the column where the date matches the date in A1.

Sub StoreToday()
Dim LCol As Long, rng As Range, c As Range

LCol = Cells(1, Columns.Count).End(xlToLeft).Column
Set rng = Range(Cells(1, 2), Cells(1, LCol))
For Each c In rng
If c.Value = Range("A1").Value Then
LCol = c.Column
Exit For
End If
Next
Set rng = Range(Cells(3, LCol), Cells(17, LCol))
rng.Value = Range("A3:A17").Value
End Sub

Mike F

 

[hard2figure]

thank you kindly!

Yes, I had to make some mods but with a few tries I was able to get the
program to work on all the sheets I wanted it to. The number of rows each
sheet tracks varies from sheet to sheet as well as the history length and it
works on all of them. A little data cleanup now (thanks to my testing) and
getting it to execute upon opening the workbook and this time consuming part
of my job has gone from a good 45 minutes to hopefully under 5 minutes (just
running the search and overwriting the data file).

Thanks again,
Chris