Home |
Search |
Today's Posts |
#1
|
|||
|
|||
auto date update when cell is occupied
Hi
I know that there are threads on thsi site trying to deal with thsi issue, and till now i am still unable to get it to work. I have been told locally that it can't bedone, but readind various threads simular to what i am tryin gto do i think there is a solution. This is what i am trying to do( i am no excel boffin by any means so please keep it simple) Cells A = invoice no Cells B = location Cells C = Payment amount due (£) Cells D = Invoice sent out (Y/N) Cells E = Invoice Paid in (amount £0.00) Cells F = Date Paid So what i am trying to sort out is when i enter the data into Cell E i want the date to automatically placed / auto enter into Cell F. This have done by using the following formula: =IF(E280,NOW()," "). but by using this formula it changes every time when i load the spread sheet on a different date i.e. entered the amount in Cell E, say on the 20th May 2011 it then automatically entres the date of that day using the formula as above, and saved it the sheet. Reopen it again say on the 25th May 2011 the date changes from the 20th to the 25th, which is not what i want. All i want is for the date to remain the same as when i entered the amaount in Cells E on the 20th, this then shows me the date of when it was either entered or paid. Please can anybody advise me on how to sort this out. Look forward to your assistance. Please keep it simple (idiots guide would great!!) Last edited by steve. : May 30th 11 at 04:46 PM |
#2
|
|||
|
|||
Quote:
Is there anybody out there that can help with this issue, i have seen that there are over 100 views but no assistance in helping this this problem!! please can somebody respond!! |
#3
|
|||
|
|||
Quote:
The first thing you need to do is to enable the Iterative Calculation capability. This stops the circular reference warning and allows a formula to refer to itself. To do this in Excel 2007 (File/Options I presume in Excel 2003) select the Window button (round button in the top left corner) and click Excel Options. Under the Formulas tab, tick the Enable Interative Formulas option. Now you can use the formula: Code:
=IF(E21,IF(F2="",NOW(),F2),"") Whilst this formula works fine, I find that it can revert back to zero (e.g. "00-Jan-00 00:00:00") if you edit the formula or do anything silly with it. So periodically it would be a safe move to Copy and Paste Values of any dates that come up using this formula just to protect any data from being lost. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Auto update date | Excel Discussion (Misc queries) | |||
Adding a date and name to auto update within one cell | Excel Worksheet Functions | |||
auto update of date | Excel Worksheet Functions | |||
How to auto update a cell with a current date | Excel Discussion (Misc queries) | |||
canceling auto date update | New Users to Excel |