Forum Discussion
Urgent Help Needed copy and pasting Values only when a condition is met
Basically, to replace a formula by a constant value requires human action or a macro.
There are tricks that give the result you require but they could cause problems using the workbook. If you allow circular references with a single step then
= IF( (date=today)*(thisCell=0), value, thisCell)
will copy the value into 'thisCell' provided it is initially blank and the dates match. Otherwise it will reference itself and retain its existing value. If you try it, I would be interested to know how you get on! Maybe I am being unduly pessimistic.
Hi Peter,
Unfortunately, this does not work. I would be very grateful if you could review my response to Nikolito as I have tried to be as clear as possible in my response.
Thank you in advance
Ian
There are some undesirable characteristics but it should work.
Firstly the final table should not use text for the date series (not an Excel Table).
The workbook must allow circular referencing.
To clear values once set the formula has to be cleared and re-entered by copying across from an adjacent cell using Ctrl/Enter.
If you set out to use VBA try to limit the transfer to reading and writing the cell values rather than copy/pasting all the formatting properties along with the value.