Cells
 

Hi all,
Could anybody help me to get the correct code for the following:
If F1= K or T or G or Y or Z or x and A1 is Null then A1 = Today().

Regards

Cells
 

There is a problem with your request. You want the formula to be in A1, and
one of the tests you want to perform (A1 is null) is that cell itself. This
creates a condition known as a circular referrence error.
A (potential) second problem is that once A1 becomes TODAY() it would not be
null any longer and the test would never meet the conditions (A1 would not be
null) - however the TODAY() would always be whatever today is, not what it
was originally.

This formula in some cell other than A1 or F1 will give you the result you
want, just not where you want it.
=IF(AND(OR(A1="",A1=0),OR(F1="K",F1="T",F1="G",F1= "Y",F1="X",F1="Z")),TODAY(),"conditions not met")

You might think at this point that you coule put a formula into A1 that just
copied the results in this cell and get your date into it, but that will not
work either. Say you put that into G1 and put =G1 into A1, then again you
get a circular reference error.

I think maybe the closest you're going to get (without resorting to VB code
in a macro or User Defined Function (UDF)) is this in A1
=IF(OR(F1="K",F1="T",F1="G",F1="Y",F1="X",F1="Z"), TODAY(),"conditions not
met")
but again, the TODAY() is always going to return the current date when a G,
K, T, X, Y or Z is in F1 and it will change from day to day.

One possible solution would be to place the following code into the
worksheet's code section (right-click on the sheet's name tab and choose
[View Code] then cut and paste this into the displayed module)

Private Sub Worksheet_Change(ByVal Target As Range)
'this works for all cells in column F
'will put today's date into column A
'on same row when cell in K matches the criteria

'you can change which column to examine
'by changing the "F1" reference in the next line
If Target.Column < Range("F1").Column Then
Exit Sub ' change not in column K
End If
'could have more than one cell as Target if
'you have selected several in the column,
'as to use [Del] to clear or range that
'also includes cells not in column K
'in this case, don't do anything either.
If Target.Cells.Count 1 Then
Exit Sub ' only work when 1 cell changes
End If
If IsEmpty(Range("A" & Target.Row)) Then
'nothing in A, so check K for value
Select Case UCase(Trim(Target))
Case Is = "G", "K", "T", "X", "Y", "Z"
Range("A" & Target.Row) = Now()
Case Else
'ignore everything else
End Select
End If
End Sub

Two other situations I can imagine you may need would be that if value in Fn
changes to or is entered as an 'invalid' character and you need to erase the
date in column A, or perhaps only erase date in A if the cell in F is empty -
if you need that kind of response, we have to change the code somewhat, to
look at Fn entries before examining contents of A. If so, say so and we can
work out the code - presuming you decide this code is the way to go.



"Pietro" wrote:

Hi all,
Could anybody help me to get the correct code for the following:
If F1= K or T or G or Y or Z or x and A1 is Null then A1 = Today().

Regards

Cells
 

Hi JLatham,
Thank you for your answer..
Actually all that i want to do is to prevent users from changing the
date..so i want that once a user of the six ones that i have selects his name
from the list in column F,today's date can be updated automaticallyin column
A,same time i want to lock and protect the column that contains the dates ,so
users may not be able to change it manually,i want also to make the date
inupdatable even if a user changes the written name .

"JLatham" wrote:

There is a problem with your request. You want the formula to be in A1, and
one of the tests you want to perform (A1 is null) is that cell itself. This
creates a condition known as a circular referrence error.
A (potential) second problem is that once A1 becomes TODAY() it would not be
null any longer and the test would never meet the conditions (A1 would not be
null) - however the TODAY() would always be whatever today is, not what it
was originally.

This formula in some cell other than A1 or F1 will give you the result you
want, just not where you want it.
=IF(AND(OR(A1="",A1=0),OR(F1="K",F1="T",F1="G",F1= "Y",F1="X",F1="Z")),TODAY(),"conditions not met")

You might think at this point that you coule put a formula into A1 that just
copied the results in this cell and get your date into it, but that will not
work either. Say you put that into G1 and put =G1 into A1, then again you
get a circular reference error.

I think maybe the closest you're going to get (without resorting to VB code
in a macro or User Defined Function (UDF)) is this in A1
=IF(OR(F1="K",F1="T",F1="G",F1="Y",F1="X",F1="Z"), TODAY(),"conditions not
met")
but again, the TODAY() is always going to return the current date when a G,
K, T, X, Y or Z is in F1 and it will change from day to day.

One possible solution would be to place the following code into the
worksheet's code section (right-click on the sheet's name tab and choose
[View Code] then cut and paste this into the displayed module)

Private Sub Worksheet_Change(ByVal Target As Range)
'this works for all cells in column F
'will put today's date into column A
'on same row when cell in K matches the criteria

'you can change which column to examine
'by changing the "F1" reference in the next line
If Target.Column < Range("F1").Column Then
Exit Sub ' change not in column K
End If
'could have more than one cell as Target if
'you have selected several in the column,
'as to use [Del] to clear or range that
'also includes cells not in column K
'in this case, don't do anything either.
If Target.Cells.Count 1 Then
Exit Sub ' only work when 1 cell changes
End If
If IsEmpty(Range("A" & Target.Row)) Then
'nothing in A, so check K for value
Select Case UCase(Trim(Target))
Case Is = "G", "K", "T", "X", "Y", "Z"
Range("A" & Target.Row) = Now()
Case Else
'ignore everything else
End Select
End If
End Sub

Two other situations I can imagine you may need would be that if value in Fn
changes to or is entered as an 'invalid' character and you need to erase the
date in column A, or perhaps only erase date in A if the cell in F is empty -
if you need that kind of response, we have to change the code somewhat, to
look at Fn entries before examining contents of A. If so, say so and we can
work out the code - presuming you decide this code is the way to go.



"Pietro" wrote:

Hi all,
Could anybody help me to get the correct code for the following:
If F1= K or T or G or Y or Z or x and A1 is Null then A1 = Today().

Regards

Cells
 

Assumptions.............

Column B is currently unlocked.

Column A is formatted as Date/Time

Worksheet is currently protected with password of "justme"

Paste this into the sheet module.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
On Error GoTo enditall
Application.EnableEvents = False
If Target.Cells.Column = 6 Then
ActiveSheet.Unprotect Password:="justme"
n = Target.Row
If Target.Value < "" _
And Target.Offset(0, -5).Value = "" Then
With Target.Offset(0, -5)
.Value = Now
.Locked = True
End With
End If
End If
enditall:
Application.EnableEvents = True
ActiveSheet.Protect Password:="justme", DrawingObjects:=True, _
Contents:=True, Scenarios:=True
End Sub


Gord Dibben MS Excel MVP

On Sat, 3 Mar 2007 15:11:15 -0800, Pietro
wrote:

Hi JLatham,
Thank you for your answer..
Actually all that i want to do is to prevent users from changing the
date..so i want that once a user of the six ones that i have selects his name
from the list in column F,today's date can be updated automaticallyin column
A,same time i want to lock and protect the column that contains the dates ,so
users may not be able to change it manually,i want also to make the date
inupdatable even if a user changes the written name .

"JLatham" wrote:

There is a problem with your request. You want the formula to be in A1, and
one of the tests you want to perform (A1 is null) is that cell itself. This
creates a condition known as a circular referrence error.
A (potential) second problem is that once A1 becomes TODAY() it would not be
null any longer and the test would never meet the conditions (A1 would not be
null) - however the TODAY() would always be whatever today is, not what it
was originally.

This formula in some cell other than A1 or F1 will give you the result you
want, just not where you want it.
=IF(AND(OR(A1="",A1=0),OR(F1="K",F1="T",F1="G",F1= "Y",F1="X",F1="Z")),TODAY(),"conditions not met")

You might think at this point that you coule put a formula into A1 that just
copied the results in this cell and get your date into it, but that will not
work either. Say you put that into G1 and put =G1 into A1, then again you
get a circular reference error.

I think maybe the closest you're going to get (without resorting to VB code
in a macro or User Defined Function (UDF)) is this in A1
=IF(OR(F1="K",F1="T",F1="G",F1="Y",F1="X",F1="Z"), TODAY(),"conditions not
met")
but again, the TODAY() is always going to return the current date when a G,
K, T, X, Y or Z is in F1 and it will change from day to day.

One possible solution would be to place the following code into the
worksheet's code section (right-click on the sheet's name tab and choose
[View Code] then cut and paste this into the displayed module)

Private Sub Worksheet_Change(ByVal Target As Range)
'this works for all cells in column F
'will put today's date into column A
'on same row when cell in K matches the criteria

'you can change which column to examine
'by changing the "F1" reference in the next line
If Target.Column < Range("F1").Column Then
Exit Sub ' change not in column K
End If
'could have more than one cell as Target if
'you have selected several in the column,
'as to use [Del] to clear or range that
'also includes cells not in column K
'in this case, don't do anything either.
If Target.Cells.Count 1 Then
Exit Sub ' only work when 1 cell changes
End If
If IsEmpty(Range("A" & Target.Row)) Then
'nothing in A, so check K for value
Select Case UCase(Trim(Target))
Case Is = "G", "K", "T", "X", "Y", "Z"
Range("A" & Target.Row) = Now()
Case Else
'ignore everything else
End Select
End If
End Sub

Two other situations I can imagine you may need would be that if value in Fn
changes to or is entered as an 'invalid' character and you need to erase the
date in column A, or perhaps only erase date in A if the cell in F is empty -
if you need that kind of response, we have to change the code somewhat, to
look at Fn entries before examining contents of A. If so, say so and we can
work out the code - presuming you decide this code is the way to go.



"Pietro" wrote:

Hi all,
Could anybody help me to get the correct code for the following:
If F1= K or T or G or Y or Z or x and A1 is Null then A1 = Today().

Regards