doing a cell reference in an "if condition"

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I'm trying to get the following to work.

in cell d1 put if statement "=if(b5>=85,c7=now(),)"

condition is that if a value is greater, then update a "fix" date in cell c7.

the spreadsheet has a series of dates and if "B5" is greater than a given value, change the old date to today.

thanks,
 
Dave,

You can't change one cell with a formula in another cell.
Your corrected formula (placed in C7) would be:
=IF(B5>=85,NOW(),"")
but you said that you wanted a "fix" date.
For that, you'd need VBA.
Right click on the sheet tab and select "View Code"
Then copy and paste the following code:

Private Sub Worksheet_Change(ByVal Target As Range)
If Range("B5") >= 85 Then
Range("C7") = Now
End If
End Sub

Now the above might still be what you want.
What it will do is insure that any time that anything is "changed"
on that sheet, if Range("B5") is greater than or equal to 85,
it'll replace "C7" with today's date.

The above is still volatile though as any change to any cell
on that sheet will cause C7 to reflect today's date & time
if B5 >= 85.

There might be a different event depending on how B5 is being
updated. If the above doesn't suit your needs, probably a different
event might be more apropos.

For a summary of events, take a look here:
http://www.cpearson.com/excel/events.htm


John

Dave W said:
I'm trying to get the following to work.

in cell d1 put if statement "=if(b5>=85,c7=now(),)"

condition is that if a value is greater, then update a "fix" date in cell c7.

the spreadsheet has a series of dates and if "B5" is greater than a given
Click to expand...
value, change the old date to today.
 
John,
thanks, I'll try it and get back to you.

What I'm trying to do is that the spreadsheet has a lot
of test procedures (numbered) and a reference completion (percentage),
and estimated completion date. If the completion is > then 85% (85), then
change completion date to today.

Later, I'll be updating the completion date if it is falling behind (that's a rainy
day project, figuring out how far behind before having to change the date).

Again, thanks for the help.

----- John Wilson wrote: -----

Dave,

You can't change one cell with a formula in another cell.
Your corrected formula (placed in C7) would be:
=IF(B5>=85,NOW(),"")
but you said that you wanted a "fix" date.
For that, you'd need VBA.
Right click on the sheet tab and select "View Code"
Then copy and paste the following code:

Private Sub Worksheet_Change(ByVal Target As Range)
If Range("B5") >= 85 Then
Range("C7") = Now
End If
End Sub

Now the above might still be what you want.
What it will do is insure that any time that anything is "changed"
on that sheet, if Range("B5") is greater than or equal to 85,
it'll replace "C7" with today's date.

The above is still volatile though as any change to any cell
on that sheet will cause C7 to reflect today's date & time
if B5 >= 85.

There might be a different event depending on how B5 is being
updated. If the above doesn't suit your needs, probably a different
event might be more apropos.

For a summary of events, take a look here:
http://www.cpearson.com/excel/events.htm


John
 
Back
Top