Updating a column only on specific date

  • Thread starter Thread starter jsollman
  • Start date Start date
J

jsollman

I'm realitivly new to excel and this seems like a simple problem but
am not figuring it out.

I have columns that correspond to days in a month and on the curen
date I need to enter the data from a column that holds the month t
date data. I dont want that data to change when the column date is les
that the current date.

this is what I have so far, it works, but gives an error message abou
circular referances when I open the workbook.

=IF(H$3=$D$2,$G4,H4)

H3:AB3 = dates in the month
D2 = Today
G4:G19 = values that update daily from another workbook
H4:AB19 = cell where I want to record daily values to view a
history of values.

I'm thinking I may need to run a script to do this but I run int
simular problems.

Thanks in advance for the help.
Jeffer

Attachment filename: dashtest2.xls
Download attachment: http://www.excelforum.com/attachment.php?postid=54491
 
the reference to H4 in your formula is the circular refrence. What your
formula says is:
If the date on the top of the column is equal to today's date, then put
the value at the beginning of the row here, otherswise put the value that is
already here here.

So, yes, it reads funny in english, becuase it doesn't make sense.

The functionality you're looking for is exactly the opposite of what
formulas are meant to do. Formulas recalulate their results any time a
value in a referenced cell changes. In you're case, it almost sounds like
you'd like the calulation to occur only if the formula is in a column that
matches today's date.

The way to rectify this is to
a) make sure to use something other than $G4 so that it resolves to
something static for dates prior to "today"
b) after you open the spreadsheet and the formula resolves for cells where
the date is today, copy the cell and paste->special->values over top of it.
c) use a macro to find the cells with today's date, and then enter the
results of your formula.
 
Back
Top