Formula that copies value at date

[Stiansen]

Hi,

I'm trying to make a progress sheet so others could see if I'm following the planned progress.
The picture shows what I've made, and what I want to do is to automatically copy the value in the red circle to the green range at the set date.

I've tried making IF(TODAY()=DATE;VALUE;"0"), but the thing is that it then returns "0" when it's not that particular date, while I want it to keep the value when the date has passed.
So I wondered if it's possible to copy the value and remove the formula, so when the date is correct the value will overwrite the formula!?

Also open for other suggestions!

asd.jpg

Thanks!!

[NickyC]

how about IF(TODAY()>=DATE;VALUE;"0")
otherwise, to change the value rather than use a formula, you can use a macro

[Stiansen]

how about IF(TODAY()>=DATE;VALUE;"0")
otherwise, to change the value rather than use a formula, you can use a macro

Thank you for answer, but if I use what you said then all the dates will get the value at that time. So the "Actual" line in the graph would be a flat line.
So what I want is a formula that deletes itself when the value in the cell i changed. Example: If the value at 1. April is "4,4", then this is copied to that cell and replaces the formula.

However, if macro is easier I can use that. But I don't have a lot of experience using it.

Thanks!

[NickyC]

Please Login or Register  to view this content.

Hi

Because you have copied a picture not a workbook it's a bit hard to see where to put stuff.

This macro assumes that the dates are in a named range called "dates",
and the value to copy is in a cell named "value".

It looks up today's date in the range "dates", and copies the contents of cell "value" into the cell immediately to the right of today's date in that range

Please Login or Register  to view this content.

If you're not sure how to run a macro:
copy the code above to clipboard
in Excel, press Alt+11 to enter the visual basic area
select insert > module if there is no module already open
paste the code into the module
in the worksheet, select tools > macros > macro and click on the macro name to run it

[Stiansen]

Works well, thank you very much and sorry that I didn't include a workbook!!

However, since I'm not working weekends and since I have a fixed date range, how can I make it look up a date +/- 3 days?
I tried with a "If Date-3 < Range("dates").Cells(n).Value < Date+3 Then" but that didn't seem to work. Tried to find out how to change the date but I couldn't figure it out!

[NickyC]

Hi
this will copy "value" next to any date +- 2 days from the current date
I have also tweaked it so it only copies "value" if the cell next to the date is empty - otherwise it might overwrite recently copied data

Please Login or Register  to view this content.

[Stiansen]

Thank you SO much!! :D